Postgres update from left join

Question

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.

Leave a Comment Cancel reply