Postgres从左联接更新

Question

我是PostgreSQL的新手，正在尝试从SQL Server转换查询。

我有一个表Users，其中包括bUsrActive，bUsrAdmin和sUsrClientCode列。我想更新用户并设置bUsrActive =
false，如果不存在另一个具有相同sUsrClientCode的用户，其中bUsrAdmin = true和bUsrActive = true。

在SQL Server中，我有此查询

UPDATE u SET u.bUsrActive = 0
FROM Users u
LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1
WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr IS NULL

我正在尝试将其转换为postgres。我写了3种方法。

1）我的第一次尝试。显然无法正常工作。

UPDATE Users u
    SET bUsrActive = false
FROM Users u2
WHERE u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = true AND u2.bUsrActive = true
AND u.bUsrAdmin = false AND u.bUsrActive = true AND u2.nkUsr IS NULL;

2）我了解为什么它不起作用（它会更新所有用户）。我只是不知道如何在UPDATE … SET部分中引用表Users u。

UPDATE Users
    SET bUsrActive = false
FROM Users u
LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = true AND u2.bUsrActive = true
WHERE u.bUsrAdmin = false AND u.bUsrActive = true AND u2.nkUsr IS NULL;

3）以下工作正常，但未使用联接。

UPDATE Users
    SET bUsrActive = false
WHERE  NOT EXISTS (
    SELECT 1
    FROM Users u
    WHERE u.sUsrClientCode = Users.sUsrClientCode AND u.bUsrAdmin = true AND u.bUsrActive = true
) AND Users.bUsrAdmin = false AND Users.bUsrActive = true;

我可能会考虑最后一种解决方案。我只是想知道是否可以使用左联接来做我想做的事情。

Answer 1

这是将更新查询从SQL服务器形式转换为PostgreSQL的一种通用方法：

UPDATE Users
 SET bUsrActive = false
WHERE
 ctid IN (
   SELECT u.ctid FROM Users u
      LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1
    WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr IS NULL
)

ctid是指向行的唯一位置的伪列。如果表有主键，则可以改用它。

问题中的查询＃2不能满足您的期望，因为更新后的表Users永远不会联接到Users uFROM子句中的同一表。就像您在FROM子句中两次放置一个表名一样，它们不会隐式地连接或绑定在一起，它们被视为两组独立的行。