title: Postgres: correlated subquery with NOT EXISTS
body: <p>In Oracle SQL, you can easily do an update based on a NOT EXISTS condition in a correlated subquery. This is useful for doing updates based on another query or list of ids.</p>
  
  <p>The mechanism for subqueries is different in Postgres... how can I achieve the same goal?
  <a href="http://sqlfiddle.com/#!1/1dbb8/55" rel="nofollow">http://sqlfiddle.com/#!1/1dbb8/55</a></p>
  
  <p><strong>How you would do it on Oracle</strong></p>
  
  <pre><code>UPDATE UserInfo a
  SET a.username = 'not found'
  WHERE NOT EXISTS (SELECT 'X' 
                FROM UserOrder b
                WHERE b.userid = a.userid)
  AND a.userid in (1,2,3);
  </code></pre>
  
  <p><strong>Postgres NOT EXISTS query: this works</strong></p>
  
  <pre><code>SELECT u.userid, u.username
  FROM UserInfo AS u
  WHERE NOT EXISTS
    ( SELECT *
      FROM UserOrder AS o
      WHERE o.userid = u.userid
  );
  </code></pre>
  
  <p><strong>Postgres NOT EXISTS update : does not work</strong></p>
  
  <pre><code>UPDATE UserInfo
  SET username = 'not found'
  FROM (SELECT u.userid
      FROM UserInfo AS u
      WHERE NOT EXISTS
         ( SELECT *
           FROM UserOrder AS o
           WHERE o.userid = u.userid
         )) em
  WHERE em.userid = UserInfo.userid;
  </code></pre>