从左连接更新 Postgres

2022-01-17 00:00:00 postgresql sql-update sql sql-server

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

I'm new in PostgreSQL and trying to convert a query from SQL Server.

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

I have a table Users with, among others, the columns bUsrActive, bUsrAdmin and sUsrClientCode. I want to update Users and set bUsrActive = false if there does not exist a another user with the same sUsrClientCode where bUsrAdmin = true and bUsrActive = true.

在 SQL Server 中我有这个查询

In SQL Server I have this query

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 种方法.

I'm trying to convert this to postgres. I wrote 3 approaches.

1) 我的第一次尝试.显然不行.

1) My first attempt. Obviously not working.

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 部分引用表用户 u.

2) I understand why it's not working (it updates all users). I just can't figure out how can I reference table Users u in the UPDATE ... SET part.

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) 以下是有效的，但不使用连接.

3) The following is working, but not using 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;

我可能会选择最后一个解决方案.我只是想知道是否可以使用左连接来做我想做的事情.

I'll probably go with the last solution. I just wanted to know if it's possible to do what I want using a left join.

推荐答案

以下是将此更新查询从 SQL-server 表单转换为 PostgreSQL 的通用方法:

Here's a generic way to transform this update query from SQL-server form to 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 是一个伪列，它指向到一行的唯一位置.如果有主键，您可以使用表的主键.

ctid is a pseudo-column that points to the unique location of a row. You could use instead the primary key of the table if it had one.

问题中的查询 #2 没有达到您的预期，因为更新的表 Users 从未在 FROM 子句中加入到同一表 Users u 中.就像您在 FROM 子句中两次输入表名一样，它们不会被隐式连接或绑定在一起，它们被视为两组独立的行.

The query #2 from the question doesn't do what you expect because the updated table Users is never joined to the same table Users u in the FROM clause. Just as when you put a table name twice in a FROM clause, they don't get implicitly joined or bound together, they are considered as two independant sets of rows.

相关文章