I want to exchange a column value in two different rows on a Microsoft SQL server

Question

I want to execute the following two SQL queries in Microsoft SQL SERVER

UPDATE Partnerships SET sortOrder = 2 WHERE sortOrder = 1; UPDATE Partnerships SET sortOrder = 1 WHERE sortOrder = 2; 

The only problem: I do not allow sortOrder to contain the same value, this is a unique key. How could I get around this because the first request violates a single key rule and ends? Or do I need to get rid of the unique key rule that I have?

Thanks!

Answer 1

Use CASE and do both lines at a time. You will need one CASE clause for each filter key value:

 UPDATE Partnerships SET sortOrder = CASE WHEN sortOrder = 1 THEN 2 ELSE 1 END WHERE sortOrder IN (1, 2) 

Slightly more ticklish:

 UPDATE Partnerships SET sortOrder = 3-sortOrder WHERE sortOrder IN (1, 2)