UPDATE rows with no match in other table

Question

I have two tables:

TABLE A
  id  |  user_id  | name
------+-----------+-------
  1   |   122     | 'Bill'
  2   |   123     | 'Jim'
  3   |   124     | 'Sally'

TABLE B
  id  |  user_id  | title
------+-----------+-------
  1   |   122     | 'Boss'
  2   |   999     | 'Manager'
  3   |   124     | 'Worker'  

I want to update all of A with name = 'foo' where there is no matching user_id in table B. Do not update the row if user_id exists in table B.

So in this case it would only update Jim to the name 'foo'.

Answer 1

NOT EXISTS should be simplest, safest & fastest:

UPDATE tbl_a a
SET    name = 'foo' 
WHERE  NOT EXISTS (SELECT FROM tbl_b b WHERE b.user_id = a.user_id);

This also works as expected with NULL values. As opposed to NOT IN. See:

• https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN