12

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
Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
hhh3112
  • 2,167
  • 10
  • 36
  • 55

4 Answers4

17

You can simply combine an update with a case statement such

UPDATE records
   SET name =
     CASE
       WHEN id = 3 THEN 'abc'
       WHEN id = 1 THEN 'def'
       ELSE name
     END
Jose Rui Santos
  • 15,009
  • 9
  • 58
  • 71
  • 2
    I think that you should have a `where` on that, otherwise it will do an update on all records in the table. – Guffa Apr 25 '11 at 12:25
  • @Guffa You have a valid point and I agree with the `where` clause. The downside of this is that we need to make sure conditions in `case` and `where` are the same. Sure, my solution updates all rows, but for the rows that don't fall under these conditions, they are updated from `name` to `name`, which obviously does not change anything – Jose Rui Santos Apr 25 '11 at 14:03
  • 5
    In most cases it doesn't change anything, but imagine that there is a trigger on update that logs all changed rows to another table... – Guffa Apr 25 '11 at 23:35
  • @Guffa - Very good point on the triggers. I suppose you should have logic in your trigger that only logs changed values, but is there perhaps a solution where only rows that meet conditions are updated? I'd like to have my cake an eat it too. – Daniel Macias Jun 12 '14 at 17:14
  • @DanielMacias: All records affected by the query will be updated, the only way to keep records from being updated is to add a `where` clause (or use a `join` to the same end). – Guffa Jun 12 '14 at 17:29
17

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
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 3
    Why the downvote? If you don't explain what it is that you think is wrong, it can't improve the answer. – Guffa Oct 22 '13 at 09:24
  • @Guffa I am interested in using the second approach. How many records do you mean by just a few records. I have got potentially a few thousand records to be updated repeatedly in intervals. – AbbasFaisal Mar 06 '18 at 14:09
5
;WITH vals(id, name)
     AS (SELECT 3,'abc'
         UNION ALL
         SELECT 1,'def')
UPDATE r
SET    name = vals.name
FROM   records r
       JOIN vals
         ON vals.id = r.id
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
4

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138