Postgresql Select rows and update column

I have SQL Select query with where clauses. For e.g

select * from table where status = 1

And how can I update single column with selected rows simultaneously while selecting? I want to mark selected rows, to avoid reselect on the next loop. Something like:

select * from table where status = 1; update table set proc = 1 where id in (select id from table where status = 1)

But this query will not return results.

Answers


Use the returning clause:

update table 
    set proc = 1 
where id in (select id from table where status = 1)
returning *;

(Btw: I assume the inner select is not actually selecting from the same table, because then the statement does not really makes sense as it could be rewritten with a simple where stauts = 1)


Need Your Help

Mysql reduce execution time of query involving multiple tables and conditions

mysql

I have 4 tables namely emp_table , project_table , dept_table , and team_table. I am storing the ids of related project, department, and team in main table, i.e emp_table. I have written the follow...