My query is as follow:
UPDATE t1 SET t1.foreign_key = (SELECT id FROM t2 WHERE t2.col = %s )
WHERE t1.col = %s
UPDATEto 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
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 = 1is invalid.