SQL 在 UPDATE 上返回更新的行属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38948800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return updated row attributes on UPDATE
提问by cppgnlearner
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?
如何在同一查询中返回表中更新行的某些属性？
采纳答案by Erwin Brandstetter
The optional
RETURNINGclause causesUPDATEto 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.
可选
RETURNING子句导致UPDATE根据实际更新的每一行计算和返回值。FROM可以计算使用表的列和/或 中提到的其他表的列的任何表达式。使用表列的新（更新后）值。
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, t1is updated anyway and t1.colis set to NULL. Typically, you'd rather notupdate the row in this case, which is what my suggested query does instead.
对于您的原始查询，如果子查询在 中找不到任何行t2，t1则无论如何都会更新并t1.col设置为 NULL。通常，在这种情况下，您宁愿不更新行，而我建议的查询正是这样做的。
BTW, target columns in the SETclause cannot be table-qualified (only onetable is updated anyway). The manual once more:
顺便说一句，SET子句中的目标列不能是表限定的（无论如何只更新一个表）。手册再次：
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.
不要在目标列的规范中包含表的名称 - 例如，
UPDATE table_name SET table_name.col = 1无效。
回答by Gordon Linoff
You can use the RETURNINGclause:
您可以使用以下RETURNING条款：
UPDATE t1
SET t1.foreign_key = (SELECT id FROM t2 WHERE t2.col = %s )
WHERE t1.col = %s
RETURNING *;
The documentationis part of the UPDATEstatement.
该文档是的一部分UPDATE声明。
