updating column in db table

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.