Swap ID's in Sql Server

I took a look at the question: sql swap primary key values

So I got the following script:

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id开发者_StackOverflow
WHERE t.id = 1 AND t2.id = 2

But i can not translate this to a valid SQL Server syntax.

Please help me :)

Thanks in advance!

What about

UPDATE t SET t.id = (CASE t.id WHEN 1 THEN 2 ELSE 1 END)
WHERE t.id IN (1, 2)

It looks like you are trying to join the table against itself so that you can update two records at once. I'm not sure if it's possible, but it's certainly not needed.

Just locate the two records, and calculate one id from the other by subtracting it from the sum of the two ids:

update t
set id = (1 + 2) - id
where id = 1 or id = 2

(Note: This requires that the key is numeric. While this is the most common case, some non-numeric types can also be used as keys.)