Re: Use select and update together

On Mon, Sep 12, 2011 at 3:36 AM, Boris <kuzikoff(at)ukr(dot)net> wrote:

>
> SELECT (
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
Just lose the select (); part.

update tbl set val = 1 where condition = true returning *;

That works just fine on 8.4. You can also specify individual columns in the
returning clause. The postgresql documentation covers the topic.

http://www.postgresql.org/docs/9.0/static/sql-update.html

It doesn't appear to work in a subquery, so you can't join to it (though you
can use a join within the update clause and then return the columns you need
from a joined table, I suspect). Simply returning 1 or more columns from
updated rows works just fine, though.