Postgres update from left join

Question

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

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.

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

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

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) 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) 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.

Solution 1

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 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.

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.

Solution 2

I think this is the correct way of doing 2) I belive it's more optimal/efficient than doing a sub select.

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;

Comments

[a_horse_with_no_name]

What's wrong with the third one?

[alfoks]

Nothing, it's working. Just wondering if I can do it the other way using joins. Seems nicer to the eye! I guess the performance would be the same.

[a_horse_with_no_name]

The second one should work (at first sight) what is the error you get?. Are you aware that the semantics of the FROM clause is different in PostgreSQL compared to SQL Server?

[alfoks]

@a_horse_with_no_name, I'm new and learning now. The second one will update all the records in Users table, not just the "Users u".

[alfoks]

Thanks for answering. It seems that what I'm asking cannot be done. I mean in case of query #2 there is not a way to reference u table somehow. Anyway I'll have in mind your solution for future reference and possible more complex queries. For now I'll go with the 3rd query.

[alfoks]

I don't have Postgres installed anymore and it's been 2 years since I last used it, but I believe your query is not syntactical correct. Otherwise my first query would work as well. If I remember correct, in Postgres you can't alias the table you are updating.

[drunken_monkey]

you can alias the table you are updating. You cannot use that alias in the SET clause.

[RedBeard]

tested against 9.5 and it works as expected, it make a lot of sense and is faster than a subquery. up.

[Raiden Core]

superb! but cant it be done without subquery at all ?