MySQLのUPDATEでサブクエリを使用したときに陥りがちな「You can't specify target table」

Web > Other 2017年12月6日（最終更新：6年前）

2017年12月6日に作成されたページです。
情報が古かったり、僕が今以上のど素人だった頃の記事だったりする可能性があります。

どもです。

MySQLにて、とあるテーブルに対し、
カラムAが一定の値である行のカラムBと一致する、全てのカラムBの行を更新対象にする。
という処理がやりたかった。

ので、UPDATE文にサブクエリを使えば行けるかな？と思い、実行。

UPDATE 対象テーブル
SET 更新したいカラム = 'xxx'
WHERE カラムB = ANY(SELECT カラムB FROM 対象テーブル WHERE カラムA='一致条件');

しかし、エラー。
#1093 - You can't specify target table 'xxxx' for update

リファレンス曰く、
「サブクエリーの FROM 句と更新のターゲットの両方に同じテーブルを使用することはできません。」

えっ困る。
そしたらSQLを2回実行して、間にPHPも挟まなきゃならなくなるじゃないか。やだよ。

と、悲しみに暮れていたところ、有力情報を発見。
なんでも、テーブルにエイリアスをつけるとこのエラーが回避できるとか。

というわけで実行。

UPDATE 対象テーブル
SET 更新したいカラム = 'xxx'
WHERE カラムB = ANY(SELECT temp FROM ( SELECT カラムB AS temp FROM 対象テーブル WHERE カラムA='一致条件' ) AS temp1);

できたよ(゜レ゜)

A.テンポラリテーブルとして扱われる！

この場合、
一時的に( SELECT カラムB AS temp FROM 対象テーブル WHERE カラムA='更新条件' ) のテーブルが
「temp1」という名称で生成され、その中からカラムB(temp)の値を取り出しているため「同じテーブル」を使用していることにならない、と。

裏ワザ的な対処法ですが、理論としては危険なことはしていなさそうですね。

…サブクエリでテンポラリテーブルまで介しているとなると、実行件数によってはだいぶ重くなりそうだけど。

調査を進めると、Stack Overflowに気になる記述が。

「ただし、 MySQL 5.7.6以降では、オプティマイザがサブクエリを最適化してエラーを返すことに注意してください。」

まぁでも、今回の対象MySQLは5.7どころか4代なので問題なし。
開発環境にて正常な動作を確認し、テスト環境にアップ！

したところ、またおいでやがりました「You can't specify target table」！

厳密な原因は探ってないですが、5.7.6未満でも上手くいかない環境があるっぽいですね。
しゃーないので別解を探しましょ。

UPDATE 対象テーブル a, (SELECT カラムB FROM 対象テーブル WHERE カラムA = '一致条件') b
SET a.更新したいカラム = 'xxx'
WHERE a.カラムB = b.カラムB;

UPDATEの方でテンポラリテーブルを作ってしまうパターンです。
こっちのが見た目は良いですね。上の解でエラーが起きた環境・起きなかった環境両方で正常な動作を確認。

この記事は役に立ちましたか？