UPDATE rows with no match in other table

exists, postgresql, sql, sql-update

Solution

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);

Problem

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

Original source