Created
August 5, 2010 01:36
-
-
Save tathamoddie/509087 to your computer and use it in GitHub Desktop.
Swap two values in SQL that have a unique constraint
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET NOCOUNT ON | |
CREATE TABLE Foo | |
( | |
Id int primary key, | |
SortIndex int unique | |
) | |
GO | |
INSERT INTO Foo VALUES (1, 10) | |
INSERT INTO Foo VALUES (2, 20) | |
INSERT INTO Foo VALUES (3, 30) | |
INSERT INTO Foo VALUES (4, 40) | |
SELECT * FROM Foo ORDER BY SortIndex | |
UPDATE Foo | |
SET SortIndex = CASE SortIndex | |
WHEN 30 THEN 20 | |
WHEN 20 THEN 30 | |
END | |
WHERE SortIndex IN (20, 30) | |
SELECT * FROM Foo ORDER BY SortIndex | |
DROP TABLE Foo | |
--Output: | |
-- Id SortIndex | |
-- ----------- ----------- | |
-- 1 10 | |
-- 2 20 | |
-- 3 30 | |
-- 4 40 | |
-- Id SortIndex | |
-- ----------- ----------- | |
-- 1 10 | |
-- 3 20 | |
-- 2 30 | |
-- 4 40 |
Dose not work in PostgreSQL 9 "error: duplicate key value violates unique constraint"
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
does not work on mysql 5.7.