title: Postgres update from left join
body: <p>I'm new in PostgreSQL and trying to convert a query from SQL Server.</p>
  
  <p>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.</p>
  
  <p>In SQL Server I have this query</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm trying to convert this to postgres. I wrote 3 approaches.</p>
  
  <p>1) My first attempt. Obviously not working.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>3) The following is working, but not using join.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>