从左连接Postgres的更新(Postgres update from left join)

我在PostgreSQL的是新的，并试图从SQL Server查询转换。

我有一个表用户，除其他外，列bUsrActive，bUsrAdmin和sUsrClientCode。 我想更新的用户，并设置bUsrActive = false，如果不存在一个其他用户使用相同的sUsrClientCode其中bUsrAdmin =真实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一部分。

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 u在FROM子句中。 就像当你在把表名两次FROM子句中，他们没有得到隐含加入或结合在一起，它们被视为两个独立的行集。

Answer 2:

我认为这是做2的正确的方式）我相信它比做一个子选择更优化/效率。

UPDATE Users uOrig
    SET bUsrActive = false
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
    and uOrig.sUsrClientCode = u.sUsrClientCode;

文章来源: Postgres update from left join