Update columns in the same table with different values with postgres

Question

This is a very basic question, but I couldn't find a better way to do this and I have the feeling that I am going about it the wrong way.

Basically I have two values in my table's column that I would like to update to two different values.

So say I have a column name with the value My Name, I would like to change it to Updated My Name, however on the same column, if I also have the value My Name222, I would like to update it to Updated My Name222. Currently I have two Update SQL calls, which look like this:

UPDATE myTable t
SET "name"='My Name' WHERE "name"= 'Updated My Name';
UPDATE myTable t
SET "name"='My Name222' WHERE "name"= 'Updated My Name222';

As I mentioned before, this does work, but I feel there is a better way to do it back to back without having to call UPDATE myTable t twice. How would that be?

Answer 1

There is a way to do this in a single statement using case:

update my_table
set name = case name 
    when 'My Name' then 'Updated My Name'
    else 'Updated My Name222'
    end
where name in ('My Name', 'My Name222');

In this case two separate updates are simpler and quite natural.

However, if you want to modify the column values exactly in the same way in both rows, then this solution makes more sense:

update my_table
set name = concat('Updated ', name)
where name in ('My Name', 'My Name222');

Answer 2

Personally, I like using values() for this sort of thing:

update my_table
    set name = newname 
    from (values ('My Name', 'Updated My Name'),
                 ('My Name222', 'Updated My Name222')
         ) v(oldname, newname)
    where name = oldname;

This has some advantages over explicit case expressions. First, it is easily generalizable -- just add more rows to v. Second, you don't have to repeat any values, reducing the opportunity to make mistakes.