在 UPDATE 上返回更新的行属性 [英] Return updated row attributes on UPDATE
问题描述
我的查询如下:
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?
推荐答案
可选的 RETURNING
子句导致 UPDATE
计算并返回基于实际更新的每一行的值.任何表达式使用表的列,和/或 FROM
中提到的其他表的列,可以被计算.使用表列的新(更新后)值.
The optional
RETURNING
clause causesUPDATE
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 inFROM
, 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
对于您的原始查询,如果子查询在 t2
中找不到任何行,则无论如何都会更新 t1
并将 t1.col
设置为 NULL.通常,在这种情况下,您宁愿不更新行,而我建议的查询正是这样做的.
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.
顺便说一句,SET
子句中的目标列不能是表限定的(无论如何只更新 一个 表).再次使用手册:
BTW, target columns in the SET
clause cannot be table-qualified (only one table is updated anyway). The manual once more:
请勿在目标规范中包含表名列 — 例如,UPDATE table_name SET table_name.col = 1
无效.
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.
这篇关于在 UPDATE 上返回更新的行属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!