Reputation: 7470
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?
Upvotes: 5
Views: 7224
Reputation: 1271231
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.
Upvotes: 6
Reputation: 121919
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');
Upvotes: 4
Reputation: 385
Another way
UPDATE tablename
SET name = CASE WHEN name = 'Updated My Name' THEN 'My Name'
WHEN name = 'Updated My Name222' THEN 'Name222'
END
where name in ('My Name', 'My Name222');
Upvotes: 1