Return updated row attributes on UPDATE

Question

My query is as follow:

UPDATE t1 SET t1.foreign_key = (SELECT id FROM t2 WHERE t2.col = %s ) 
WHERE t1.col = %s

How do I return some attributes of the updated row in the table in the same query?

Answer 1

Use the RETURNING clause.

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used.

But typically it's smarter to use a join instead of a correlated subquery:

UPDATE t1
SET    foreign_key = t2.id
FROM   t2
WHERE  t2.col = %s
AND    t1.col = %s
RETURNING t1.*;   -- or only selected columns

With your original query, if the subquery finds no row in t2, t1 is updated anyway and t1.col is set to NULL. Typically, you'd rather not update the row in this case, which is what my suggested query does instead.

BTW, target columns in the SET clause cannot be table-qualified (only one table is updated anyway). The manual once more:

Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid.