带连接的Postgres RETURNING子句

本文介绍了带连接的Postgres RETURNING子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在下面的SQL中，如何使RETURNING子句联接到其他对象并返回联接的行?在这里，它只返回更新后的mytable中的行，但我希望它返回连接到另一个表中某行的行.

In the following SQL, how could I make the RETURNING clause join to something else and return the joined row(s)? Here it only returns the row from mytable that was updated, but I'd like it to return that row joined to something in another table.

UPDATE mytable SET status = 'A' FROM ( SELECT myid FROM mytable WHERE status = 'B' ORDER BY mycolumn LIMIT 100 FOR UPDATE ) sub WHERE mytable.myid = sub.myid RETURNING *;

我可以从客户端应用程序中进行另一个查询，但是我想知道是否有一种方法可以从Postgres中进行，而不必进行一次单独的DB往返.

I could do another query from my client application, but I'd like to know if there's a way to do it from within Postgres within having to make a separate roundtrip to the DB.

推荐答案

FROM子句中的任何内容对于RETURNING都是公平的:

Anything in the FROM clause is fair game for RETURNING:

UPDATE mytable SET status = 'A' FROM ( SELECT myid FROM mytable WHERE status = 'B' ORDER BY mycolumn LIMIT 100 FOR UPDATE ) sub JOIN jointable j ON j.id = sub.myid WHERE mytable.myid = sub.myid RETURNING mytable.mycolumn, j.othercolumn ;