Swap two values in SQL that have a unique constraint

Swap unique values.sql

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

does not work on mysql 5.7.

Dose not work in PostgreSQL 9 "error: duplicate key value violates unique constraint"

Is there a solution for this besides using a temp value?

No idea. You're commenting on a Gist from 15 years ago, which has no doco about which engine it was even written for.