SQL single update statement multiple rows with different values where unique id matches

Question

I am trying to achieve a single update statement that would achieve the same results as below. Right now I compile this in php where there are 700+ statements created. There has to be somehow I can update all of the records with their values where the id matches in just one statement.

UPDATE `table` SET `val1` = 1, `val2` = 2, `val3` = 3 WHERE `id` = 1;
UPDATE `table` SET `val1` = 4, `val2` = 5, `val3` = 6 WHERE `id` = 2;
UPDATE `table` SET `val1` = 7, `val2` = 8, `val3` = 9 WHERE `id` = 3;
etc...

Something like this on the microsoft site looks about what I am looking for but seems like it would be very robust

UPDATE dbo.DimEmployee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00) 
       END
    ) 
WHERE SalariedFlag = 0;

and I would need it to be more like below which Im not sure if it is doable.

UPDATE 'table'
SET val1,val2,val2 =
    (CASE
         WHEN (id = 1) THEN val1 = 1, val2 = 2, val3 = 3,
         WHEN (id = 2) THEN val1 = 4, val2 = 5, val3 = 6,
         WHEN (id = 3) THEN val1 = 7, val2 = 8, val3 = 9
         ELSE // do nothing
       END
    )

Your answer [here](http://stackoverflow.com/questions/6734231/mysql-update-case-help) ( end [here](http://stackoverflow.com/questions/13673890/mysql-case-to-update-multiple-columns) ) — fusion3k, Feb 10 '16 at 16:40

Kritner · Answer 1 · 2016-02-11T11:30:50.410

You can set up a table with IDs and values that should be updated.

Example:

declare @idsAndValues table (
    id int primary key,
    val1 int,
    val2 int,
    val3 int
)

insert into @idsAndValues (id, val1, val2, val3)
select 1, 1, 2, 3
union all 
select 2, 4, 5, 6
union all
select 3, 7, 8, 9

Then your update statement:

update table
set 
    val1 = temp.val1,
    val2 = temp.val2,
    val3 = temp.val3
from table
inner join @idsAndValues temp on table.id = temp.id

There still has to be away to do this without a temp table though, No?

Yeah you can, I just find it the easiest to work with separate tables. You could instead do:

update table
    set 
        val1 = temp.val1,
        val2 = temp.val2,
        val3 = temp.val3
    from table
    inner join (
        select 1 as Id, 1 as val1, 2 as val2, 3 as val3
        union all 
        select 2, 4, 5, 6
        union all
        select 3, 7, 8, 9
    ) temp on table.id = temp.Id

I like doing it this way (either temp table or subquery) as opposed to case for a few reasons:

I find it easier to mentally parse the intent of the code (even more so with the separate table) when compared to the crazyness that could become of case
This does targeted row updates (only updates the rows that need updating). When doing updates with the case method, you'll usually see an else that sets the row values back to themselves. While this is technically OK, it feels a bit dirty to me.

This makes sense. So create a temp table with values then join the update with the id of the temp table? I will try this and let you know. There still has to be away to do this without a temp table though, No? — DOfficial, Feb 10 '16 at 16:59

score 0 · Answer 2 · answered Feb 10 '16 at 16:10

0

try

UPDATE `table` SET `val1` = (`id`-1)*3+1, `val2` = (`id`-1)*3+2, `val3` = (`id`-1)*3+3;

answered Feb 10 '16 at 16:10

Yurich

577
3
17

Those are just example values for demonstration. It is not the exact query I am trying to achieve just an example. The values are actually strings and I need to know how to update multiple rows with different values where an Id matches – DOfficial Feb 10 '16 at 16:24

SQL single update statement multiple rows with different values where unique id matches

2 Answers2