I have an int id column that I need to do a one-time maintenance/fix update on.
For example, currently the values look like this:
1
2
3
I need to make the following change:
1=3
2=1
3=2
Is there a way to do this in one statement? I keep imagining that the update will get confused if say the change from 1=3 to occurs then when it comes to 3=2 it will change that 1=3 update to 3=2 which gives
Incorrectly:
2
1
2
If that makes sense, rod.
All of the assignments within an UPDATE statement (both the assignments within the SET clause, and the assignments on individual rows) are made as if they all occurred simultaneously.
So
UPDATE Table Set ID = ((ID + 1) % 3) + 1
(or whatever the right logic is, since I can't work out what "direction" is needed from the second table) would act correctly.
You can even use this knowledge to swap the value of two columns:
UPDATE Table SET a=b,b=a
will swap the contents of the columns, rather than (as you might expect) end up with both columns set to the same value.
What about a sql case statement (something like this)?
UPDATE table SET intID = CASE
WHEN intID = 3 THEN 2
WHEN intID = 1 THEN 3
WHEN intID = 2 THEN 1
END
This is how I usually do it
DECLARE @Update Table (@oldvalue int, @newvalue int)
INSERT INTO @Update Values (1,3)
INSERT INTO @Update Values (2,1)
INSERT INTO @Update Values (3,2)
Update Table
SET
yourField = NewValue
FROM
table t
INNER JOIN @Update
on t.yourField = @update.oldvalue
In the past, I've done stuff like this by creating a temp table (whose structure is the same as the target table) with an extra column to hold the new value. Once I have all the new values, I'll then copy them to the target column in the target table, and delete the temp table.
