I am trying to update thousands of rows in my table in postgres but I am getting a user does not exist error, hence my table is not updating. How do I update the rows in table to ignore any errors where a user does not exist in the user table
x
UPDATE table
SET userid =1, WHERE app = 'aaa'
SET userid =2, WHERE app = 'bbb'
SET userid =3, WHERE app = 'aaa'
SET userid =4, WHERE app = 'ccc'
SET userid =5, WHERE app = 'aaa'
SET userid =6, WHERE app = 'bbb'
If for example userid=3 does not exist, how do I ignore any errors and update my rows. Thanks
Advertisement
Answer
I would use values()
:
update t
set userid = v.userid
from (values ('aaa', 1), ('bbb', 2), . . .
) v(app, userid)
where v.app = t.app;