Unique SQL update statement multiple rows with different values ​​where the unique ID matches

advertisements

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
    )


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:

  1. 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
  2. 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.