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.
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
casemethod, you'll usually see an
elsethat sets the row values back to themselves. While this is technically OK, it feels a bit dirty to me.