How do I update row in postgessql to ignore error?

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

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

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;

Advertisement

Answer