SQL で連番をふる
背景
Prisma で既存のテーブルへ not null & unique 制約のついた priority カラムを追加した。
このカラムは優先順位を表す int 型のカラムで 1 からのユニークな連番を降る想定。
追加するテーブルにはレコードが登録されているので、マイグレーションするためには事前に値を連番で降っておく必要があった。
以下のテーブルを
id | name |
|---|---|
17233654-e91b-1105-0a54-e99488a2e560 | りんご |
a78996db-ed8f-5026-75f7-3460d561f4ae | バナナ |
e3136101-bb27-70f8-4181-3a7f937ed939 | メロン |
次のようにしたい
id | name | priority |
|---|---|---|
17233654-e91b-1105-0a54-e99488a2e560 | りんご | 1 |
a78996db-ed8f-5026-75f7-3460d561f4ae | バナナ | 2 |
e3136101-bb27-70f8-4181-3a7f937ed939 | メロン | 3 |
解決方法
WITH 句を使って以下のような「更新対象のレコードの id を保持する id カラム」と「連番の値を保持する priority カラム」を持つ CTE を作成する。
priority は window 関数の row_number を使うことで連番になるようにしている。
id | priority |
|---|---|
17233654-e91b-1105-0a54-e99488a2e560 | 1 |
a78996db-ed8f-5026-75f7-3460d561f4ae | 2 |
e3136101-bb27-70f8-4181-3a7f937ed939 | 3 |
あとは update from を使って CTE から同じ id を持つレコードの priority を使って更新する。
WITH new_priority_table AS (
SELECT
id,
(
row_number() OVER ()
) AS priority
FROM
foo
)
UPDATE
foo
SET
foo.priority = new_priority_table.priority
FROM
new_priority_table
WHERE
foo.id = new_priority_table.id;
補足
なお、今回は連番の順番は何でも良かったので over () の条件は何も指定していませんので、結果がランダムになる可能性があります。
連番の順番を指定したい場合は over (order by name) など条件を指定する必要があります。