CODEWITHSUNDEEP
sqlpostgresqlsql-updateexists
joncodo
joncodo

Reputation: 2328

UPDATE rows with no match in other table

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'.

Upvotes: 5

Views: 4921

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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:

Upvotes: 9

MrYoshiji
MrYoshiji

Reputation: 54882

With Rails:

user_ids_in_table_b = MobelB.pluck(:user_id)

ModelA.where.not(user_id: user_ids_in_table_b).update_all(name: 'foo')

You need to change the MobelA & MobelB models name to your models name in your app.

In pure SQL:

UPDATE table_a 
SET name = 'foo' 
WHERE user_id NOT IN (SELECT user_id FROM table_b);

Upvotes: 4

Related Questions