在 UPDATE 上返回更新的行属性

本文介绍了在 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 子句.

可选的 RETURNING 子句导致 UPDATE 计算并返回基于实际更新的每一行的值.任何表达式使用表的列，和/或 FROM 中提到的其他表的列，可以被计算.使用表列的新(更新后)值.

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

对于您的原始查询，如果子查询在 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.