MySQLでこういったテーブルとデータがあったとする。データに任意の順番をもたせたいが、順番の値は重複を許可したくないといったケースを想定している。下記テーブルでいくと sort カラムが順番の値を保持しているものとする。

create table test(
    id int,
    test_id int,
    sort int,
    unique(test_id, sort)
);

INSERT INTO `test` (`id`, `test_id`, `sort`)
VALUES
	(1, 1, 0),
	(2, 1, 1),
	(3, 1, 2);

idが3のレコードのsortを1にしたい場合、普通にupdateしようとすると、複合ユニークの制約によりエラーとなってしまう😭

しかし、下記のようにSQLを発行すると、値を重複させることなく更新をかけることが可能となる。

UPDATE test SET sort = (sort + 1) WHERE sort >= 1 ORDER BY sort DESC;
UPDATE test SET sort = 1 WHERE id = 3;

sort の値が1以上の値にすべて1をプラスし、空いた1のポジションにidが3のレコードが滑り込んでいるような形である。

最初のupdate文でORDER BY sort DESCを指定しているが、この指定があることで降順にレコードにupdateをかけられる。 ASC で処理をした場合は、もちろん複合ユニークの制約に引っかかってしまい、エラーとなってしまう。

ORDER BY 句が指定されている場合は、指定されている順序で行が更新されます。

また、これとは別に、特定のtest_idのレコードはすべてdeleteし、sortの値を振り直してinsertし直すといった洗替する方法も考えられる。

最初、バルクアップデート的なものでいけないかなと考え実際にやってみたが、残念ながらユニーク制約にひっかかってしまった。