Is there a way to combine multiple update requests into a single request?

Question

I need to update the description information for each province code in the Provinces table. I'm doing it:

update Provinces set Description='Mississippi' where Code = 'MS' update Provinces set Description = 'Maryland' where Code = 'MD' .... --around 100 more update queries like that. 

So there are duplicate lines of code that don't suit me. I wonder if there is a better and more professional way to accomplish the same task. Any help or advice would be appreciated.

Answer 1

You can use CASE :

 update Provinces set Description= CASE code WHEN 'MS' THEN 'Mississippi' WHEN 'MD' THEN 'Maryland' -- ELSE 'some default value' --otherwise NULL END where Code IN('MS', 'MD'); 

---

Another approach is to create a table variable and use the UPDATE FROM JOIN :

 DECLARE @t AS (code VARCHAR(10), desc VARCHAR(1000)); INSERT INTO @t(code, desc) VALUES ('MS','Mississippi'), ('MD', 'Maryland'); UPDATE p SET desc = t.desc FROM Provinces p JOIN @tt ON p.Code = t.code; 

Answer 2

An alternative to using a case expression is to use a regular table expression expression with a table value constructor and join cte as follows:

 ;with ProvinceDescription as ( select Description, Code from (values ('Mississippi','MS') ,('Maryland','MD') ) v (Description, Code) ) update p set p.Description = pd.Description from Provinces p inner join ProvinceDescription pd on p.Code = pd.Code where p.Description <> pd.Description; 

Answer 3

If you have your unique restrictions set, you can use:

 INSERT INTO Provinces (Description, Code) VALUES ('Maryland', 'MD'), ('Mississippi', 'MS') ON DUPLICATE KEY UPDATE Description=VALUES(Description), Code=VALUES(Code)