hoge テーブルの serial カラムにある文字列「－」（全角ハイフン）を、「-」（半角ハイフン）に変換したいという場合に、同じテーブルをサブクエリ内に書くと ERROR 1093 が出るので、その対策を備忘録としてポストします。
「あれ、またこのエラーが出た…」
LAMP環境でシステム開発をしている皆さん、 MySQL の UPDATE 文で サブクエリ を使おうとした時に、こんなエラーメッセージに遭遇したことはありませんか？
ERROR 1093 (HY000): You can’t specify target table ‘テーブル名’ for update in FROM clause
この ERROR 1093 は、 MySQL の UPDATE 文におけるちょっとした落とし穴なんです。
今回は、このエラーの根本的な原因と、具体的な解決策を初心者の方にも分かりやすく解説していきます。
なぜ ERROR 1093 が発生するのか？
このエラーメッセージが示すのは、「FROM句で指定したテーブルを、同じ UPDATE 文の中で更新対象として指定することはできない」という MySQL のルールです。
つまり、 サブクエリ で参照しているテーブル自身を、その サブクエリ の結果を使って直接更新しようとすると、このエラーが発生してしまうんですね。
例を見てみましょう。もしあなたが、あるテーブルの平均値を計算する サブクエリ の結果を使って、そのテーブルの特定のカラムを更新したいと考えたとします。
|
1 2 3 4 5 6 7 |
UPDATE テーブル名 SET カラム名 = ( SELECT AVG(別のカラム名) FROM テーブル名 ); -- これはERROR 1093になります！ |
このように、更新しようとしている「テーブル名」と、 サブクエリ の FROM 句で参照している「テーブル名」が同じであるために、 MySQL は処理を拒否するわけです。
もう少しわかりやす例を出しましょう。
冒頭で書いた、全角ハイフンを、半角ハイフンに変換するSQLです。
|
1 2 3 4 5 6 7 |
UPDATE hoge SET serial = REPLACE(serial, "－", "-") WHERE id IN ( SELECT id FROM hoge WHERE serial LIKE '%－%' ); |
この書き方では、先程も述べましたが、ERROR 1093 となってしまいます。
エラーの理由は以下ページを参照。
サブクエリーは SELECT ステートメントだけでなく、UPDATE および DELETE ステートメント内でも正当であるため、UPDATE ステートメント内の割り当てのためにサブクエリーを使用できます。ただし、サブクエリーの FROM 句と更新のターゲットの両方に同じテーブル (この場合は、テーブル t1) を使用することはできません。
要約すると、同じテーブルに対するサブクエリからのID指定などの操作は出来ないようです。
ERROR 1093 を解決するための2つの方法
では、この厄介な ERROR 1093 に遭遇した場合、どうすれば良いのでしょうか？
主に2つの有効な解決策があります。
- 一時テーブル（TEMPORARY TABLE）の利用
一時テーブル を使うのは、このエラーを回避するための定番の方法の一つです。
手順としては、まず サブクエリ の結果を 一時テーブル に格納し、その 一時テーブル を使って元のテーブルを UPDATE します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 1. サブクエリの結果を一時テーブルに格納 CREATE TEMPORARY TABLE temp_table AS ( SELECT AVG(別のカラム名) AS average_value FROM テーブル名 ); -- 2. 一時テーブルを使って元のテーブルをUPDATE UPDATE テーブル名 SET カラム名 = (SELECT average_value FROM temp_table); -- 3. 一時テーブルを削除（任意） DROP TEMPORARY TABLE temp_table; |
この方法のメリットは、元のテーブルを参照している サブクエリ と、更新対象のテーブルを分離できるため、 MySQL の制約に引っかからないことです。
- 派生テーブル（Derived Table）の利用
もう一つの方法は、 サブクエリ を FROM 句の中でエイリアスをつけて派生テーブルとして扱う方法です。これにより、 MySQL は サブクエリ の結果を一時的なテーブルとして認識し、元のテーブルへの直接的な参照を回避できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
UPDATE テーブル名 SET カラム名 = ( SELECT average_value FROM ( SELECT AVG(別のカラム名) AS average_value FROM テーブル名 ) AS derived_table ); |
この方法も、 サブクエリ で参照しているテーブルと更新対象のテーブルを論理的に分離することで、 ERROR 1093 を回避することができます。
まとめ
MySQL の UPDATE 文で サブクエリ を使う際に発生する ERROR 1093 は、 MySQL のテーブル参照に関する制約によるものです。
しかし、 一時テーブル を利用したり、 サブクエリ を派生テーブルとして扱うことで、このエラーをスマートに回避し、目的の UPDATE 処理を実行することができます。
今回の解説が、皆さんの MySQL 開発の一助となれば幸いです。
関連するかもしれないポスト
