Postgres RETURNING clause with join

Question

In the following SQL, how could I make the RETURNING clause join to something else and return the joined row(s)? Here it only returns the row from mytable that was updated, but I'd like it to return that row joined to something in another table.

UPDATE mytable
SET    status = 'A' 
FROM  (
   SELECT myid
   FROM   mytable
   WHERE  status = 'B'
   ORDER BY mycolumn
   LIMIT  100
   FOR   UPDATE
   ) sub
WHERE  mytable.myid = sub.myid
RETURNING *;

I could do another query from my client application, but I'd like to know if there's a way to do it from within Postgres within having to make a separate roundtrip to the DB.

The row in sub consists of one column, which is in mytable. As written, the question is overly simplified. — Gordon Linoff
– Gordon Linoff, Commented Dec 17, 2016 at 22:25

teppic · Accepted Answer · 2016-12-17 21:26:00Z

28

Anything in the FROM clause is fair game for RETURNING:

UPDATE mytable
SET status = 'A'
FROM
  (
    SELECT
      myid
    FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.myid
WHERE mytable.myid = sub.myid
RETURNING mytable.mycolumn, j.othercolumn
;

edited Dec 17, 2016 at 21:26

answered Dec 17, 2016 at 21:09

teppic

7,3161 gold badge34 silver badges38 bronze badges

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

1 Comment

user779159 Over a year ago

Could you please take a look at my question stackoverflow.com/questions/41387603/…? It's about the same SQL statement you've listed here, except I can't get it to follow the ORDER BY mycolumn in the outer RETURNING query. Thanks!

Collectives™ on Stack Overflow

Postgres RETURNING clause with join

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related