Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres update from left join

Tags:

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.

like image 819
alfoks Avatar asked Jan 24 '13 09:01

alfoks


People also ask

Can we use join in UPDATE query in PostgreSQL?

The JOIN operator in Postgresql is used to match and combine records from different tables. If we use a JOIN in an UPDATE statement, we can perform a cross-table update which means that a record in a table will be changed if a value matched records from the second table.

What is select for UPDATE Postgres?

The select ... for update acquires a ROW SHARE LOCK on a table. This lock conflicts with the EXCLUSIVE lock needed for an update statement, and prevents any changes that could happen concurrently. All the locks will be released when the transaction ends.


2 Answers

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.

like image 114
Daniel Vérité Avatar answered Sep 20 '22 16:09

Daniel Vérité


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;
like image 39
Clive Paterson Avatar answered Sep 20 '22 16:09

Clive Paterson