I want to exchange the Value of a column in two different rows in Microsoft SQL server

Question

Hi,

I want to do 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 is, I don't allow for sortOrder to contain the same value, it is a unique key. How could I get around this, because the first query violates the unique key rule and terminates? Or will I have to get rid of the unique key rule I have?

Thanks!

Answer 1

Use a CASE and do both rows in one go. You'd need one CASE clause per filter key value:

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

Slightly cheekier:

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