Is there a way to merge multiple update queries into one query?

Question

I need to update the description information for each province code in Provinces table. What I am doing is:

update Provinces
set Description='Mississippi'
where Code = 'MS'

update Provinces
set Description = 'Maryland'
where Code = 'MD'

.... --around 100 more update queries like that.

In this way, there is a repeated lines of code, which does not look good to me. I wonder if there is any better and more professional way to do the same task. Any help or advice would be appreciated.

Answer 1

An alternative to using case expression is to use a common table expression with a table value constructor and join to the cte like so:

;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 2

You could 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 table variable and use UPDATE FROM JOIN syntax:

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 @t t
  ON p.Code = t.code;