どうやってやるのか全くわからず、悩みました...
まあおそらくSQL以外でやってみるのも手だったのだと思うのですが、
単純に好奇心でどうやってやるのか調べました。
前提
下記のようなテーブルがあります。
testテーブル
| id (PK) | another_id | target_id |
|---|---|---|
| 1 | 1 | NULL |
| 2 | 1 | NULL |
| 3 | 1 | NULL |
| 4 | 2 | NULL |
| 5 | 2 | NULL |
| 6 | 2 | NULL |
| 7 | 3 | NULL |
| 8 | 4 | NULL |
| 9 | 4 | NULL |
| 10 | 4 | NULL |
| 11 | 5 | NULL |
やりたいこと
BEFORE
下記のように重複カウントの値を一時的に取得し、
その値を持ってtarget_idカラムのNULL値をUPDATEする。
| id (PK) | another_id | target_id | duplicate_cnt |
|---|---|---|---|
| 1 | 1 | NULL | 1 |
| 2 | 1 | NULL | 2 |
| 3 | 1 | NULL | 3 |
| 4 | 2 | NULL | 1 |
| 5 | 2 | NULL | 2 |
| 6 | 2 | NULL | 3 |
| 7 | 3 | NULL | 1 |
| 8 | 4 | NULL | 1 |
| 9 | 4 | NULL | 2 |
| 10 | 4 | NULL | 3 |
| 11 | 5 | NULL | 1 |
AFTER
| id (PK) | another_id | target_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 2 | 3 |
| 7 | 3 | 1 |
| 8 | 4 | 1 |
| 9 | 4 | 2 |
| 10 | 4 | 3 |
| 11 | 5 | 1 |
やりかた
ユーザー変数を使用する.
下記が全てになります.
SELECT test.id, @i:=IF(test.another_id = @id, @i + 1, 1) AS num, @id:=test.another_id AS another_id_tmp FROM test, (SELECT @i:=0, @id:=0) AS init
@i、@idを0で初期化したinitテーブルを一時的に作成し、
そのユーザー変数のひとつである@idに重複値をカウントしたい対象カラムをSETします。
あとはもうひとつのユーザー変数、@iにif文で同じanother_idの値だったら+1していきます。違う場合は1に戻らせます。
これが可能な理由は、ユーザー変数は一旦値を保存することで現在のステートメントから次のステートメントに移行させることができるためです。
よって、SELECTがうまくいっている場合は、あとはUPDATEの条件式を以ってJOINするだけです。
UPDATE test JOIN (SELECT test.id, @i:=IF(test.another_id = @id, @i + 1, 1) AS num, @id:=test.another_id AS another_id_tmp FROM test, (SELECT @i:=0, @id:=0) AS init) as tmp SET test.target_id = tmp.num WHERE test.id = tmp.id
