Postgresql Select rows and update column

Question

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.

There is only one table involved? BTW: don't name your tables table; invent some other meta-syntactic names. — wildplasser
– wildplasser, Commented Jun 13, 2012 at 11:13

user330315 · Accepted Answer · 2012-06-13 11:13:45Z

3

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)

answered Jun 13, 2012 at 11:13

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Select rows and update column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related