MySQLで列内のすべての値をJohn1、John2、John3のように連番付きで一括更新する方法

列内のすべての値を「John1」「John2」「John3」のように更新したい場合は、1、2、3といった連番の値を設定し、それを各レコードに連結する必要があります。本記事では、ユーザー定義変数を使ってこの処理を実現する手順を、実際のSQL文と実行結果とともにわかりやすく解説します。

1. テーブルの作成

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable
(
    StudentId varchar(80)
);
Query OK, 0 rows affected (0.50 sec)

2. テストデータの挿入

次に、insertコマンドを使ってテーブルにレコードを挿入します。ここでは例として、まったく同じ名前「John」を5件登録しています。

mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.08 sec)

select文で、テーブル内のすべてのレコードを確認してみましょう。

mysql> select *from DemoTable;

実行すると、次のような出力が得られます。

+-----------+
| StudentId |
+-----------+
|       John |
|       John |
|       John |
|       John |
|       John |
+-----------+
5 rows in set (0.00 sec)

3. 連番を付けて更新するクエリ

以下が、すべての名前に連番の数値を付けて更新(連結)するクエリです。

mysql> update DemoTable,(select @row := 0) r set StudentId =concat('John',@row := @row+ 1);
Query OK, 5 rows affected (0.11 sec)
Rows matched: 5 Changed: 5 Warnings: 0

クエリの仕組み

このUPDATE文のポイントは、ユーザー定義変数「@row」の活用です。

(select @row := 0) r の部分で、クロス結合されるサブクエリを通じて変数を0で初期化します。その後、各行が更新されるたびに @row := @row + 1 が評価され、変数の値が1ずつ増加していきます。最後にCONCAT関数で「John」と増加中の数値を連結することで、John1、John2、John3…という形式の値が順番に生成される仕組みです。

4. 更新結果の確認

テーブルのレコードをもう一度確認してみましょう。

mysql> select *from DemoTable;

実行すると、次のようにすべての値に連番が付与されていることがわかります。

+-----------+
| StudentId |
+-----------+
|      John1 |
|      John2 |
|      John3 |
|      John4 |
|      John5 |
+-----------+
5 rows in set (0.00 sec)

補足：MySQL 8.0以降での注意点

ユーザー定義変数をSELECT文やUPDATE文の中で値を代入しながら使う手法は、MySQL 8.0以降では非推奨(deprecated)とされており、動作が保証されない場合があります。MySQL 8.0以降の環境では、ROW_NUMBER()などのウィンドウ関数を使った方法への移行を検討するとよいでしょう。

まとめ

ユーザー定義変数とCONCAT関数を組み合わせることで、重複した値を持つ列に対して、SQL文ひとつで簡単に連番付きの値を一括設定できます。テストデータの整理や、一意な表示名を付けたい場合などにぜひ活用してみてください。