INNER JOIN UPDATE，WHERE更新Postgres中的每条记录

I'm trying to update a single record and return the email of the owner of the updated record by doing INNER JOIN de ID of the createdby which links to the users table.

我正在尝试更新单个记录并通过执行链接到users表的createdby的INNER JOIN de ID来返回更新记录的所有者的电子邮件。

Here's the query:

这是查询：

UPDATE requests
SET statusid = 2
FROM requests AS r
INNER JOIN users
ON r.createdby = users.id
WHERE r.id =10
RETURNING r.id, users.email;

But this will update every record instead of using the WHERE.

但这会更新每条记录而不是使用WHERE。

What am I doing wrong?

我究竟做错了什么？

1 个解决方案

#1

In Postgres, you do not repeat the table being updated in the FROM. So:

在Postgres中，您不会重复在FROM中更新的表。所以：

UPDATE requests r
    SET statusid = 2
    FROM users u
    WHERE r.createdby = u.id AND r.id =10
RETURNING r.id, u.email;