Run more than one update statement at one time?-CodePudding

UPDATE TableName  SET 'OldName1' = 'New Name1' WHERE ID = 1
UPDATE TableName  SET 'OldName2' = 'New Name2' WHERE ID = 2
UPDATE TableName  SET 'OldName3' = 'New Name3' WHERE ID = 3`
UPDATE TableName  SET 'OldName4' = 'New Name4' WHERE ID = 4

CodePudding user response：

You can perform a single update by using a case expression, like so:

update TableName set
  OldName1 = case when id = 1 then 'New Name1' else OldName1 end
  OldName2 = case when id = 2 then 'New Name2' else OldName2 end
  OldName3 = case when id = 3 then 'New Name3' else OldName3 end
  OldName4 = case when id = 4 then 'New Name4' else OldName4 end
where Id between 1 and 4;

CodePudding user response：

You have a "set" of rows you want to update. That idea should lead you to using a table in some fashion. In this case we can use a temp table of some type or a table value constructor. The constructor is a very handy of dynamically creating a table for a single query.

You put your rows of "updates" in such a constructor and then simply join to it for the update. Example:

update tod 
set name = v.name 
from tod inner join (values (1, 'new bbb'), (2, 'new ccc'), 
   (3, 'new ddd'), (4, 'new zzz')) v(id, name)
on tod.id = v.id;

Fiddle to demonstrate. Here "tod" is your table aptly named "TableName".