Update multiple records in SQL

Question

How can i update multiple records in a single statement like this with SQL?:

UPDATE records
   SET name='abc' where id=3,
   SET name='def' where id=1

Answer 1

Standard SQL:2003 syntax (works on SQL Server 2008 onwards):

MERGE INTO records 
   USING (
          VALUES (1, 'def'), 
                 (3, 'abc')
         ) AS T (id, name)
      ON records.id = T.id
WHEN MATCHED THEN
   UPDATE 
      SET name = T.name;

Note that NAME and RECORDS are SQL reserved words.

Answer 2

For just a few records, you could use:

update records
set name = case id
  when 1 then 'def'
  when 3 then 'abc'
end
where id in (1, 3)

A bit more flexible is to create a result that you can join into the update:

update r
set name = x.name
from records r
inner join (
  select id = 1, name = 'abc' union all
  select 3, 'def' union all
  select 4, 'qwe' union all
  select 6, 'rty'
) x on x.id = r.id