Postgres左连接更新

Question

我是PostgreSQL的新手,正试图将一个SQL Server查询转换过来。

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

在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

我试图将这个转换为PostgreSQL。我写了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) 以下代码可以正常工作，但未使用join函数。

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;

我可能会选择最后一种解决方案。我只是想知道是否可以使用左连接来实现我想要的内容。

- alfoks

---

第三个有什么问题？ - user330315

没事，它能正常工作。只是在想是否可以使用连接的方式来完成它。这样看起来更加美观！我猜性能应该是一样的。 - alfoks

第二个应该可以工作（乍一看），你得到的错误是什么？您是否知道在PostgreSQL中，FROM子句的语义与SQL Server不同？ - user330315

@a_horse_with_no_name，我是新手正在学习。第二个将更新Users表中的所有记录，而不仅仅是“Users u”。 - alfoks

Answer 1

以下是将这个 SQL Server 更新查询转换为 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在FROM子句中从未与同一张表Users u进行关联。就像当您在FROM子句中两次放置表名时，它们不会被隐式地连接或绑定在一起，它们被视为两个独立的行集。

- Daniel Vérité

---

感谢您的回答。看来我所问的问题无法解决。我的意思是，在第二个查询中，没有办法以某种方式引用u表。无论如何，我会记住您的解决方案，以备将来参考和可能更复杂的查询。现在，我将使用第三个查询。 - alfoks

太棒了！但是完全没有子查询的话，这个能不能做到？ - Raiden Core

Answer 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;

- Clive Paterson

---

我已经两年没用 PostgreSQL 了，也没有安装它了，但我认为你的查询语句不正确。否则我的第一个查询也可以正常工作。如果我没记错的话，在 PostgreSQL 中，你不能给要更新的表取别名。 - alfoks

你可以给你正在更新的表起个别名。但你不能在SET子句中使用该别名。 - drunken_monkey

已经在9.5版本上进行了测试，符合预期，并且比子查询更加合理和快速。向上。 - RedBeard