ど素人から毛を生やす。<延>

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

Web > Other 2017年12月6日(最終更新:0日前)

どもです。

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)の値を取り出しているため「同じテーブル」を使用していることにならない、と。

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

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

MySQLのverか設定次第でダメなようなので別解を探す

調査を進めると、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の方でテンポラリテーブルを作ってしまうパターンです。
こっちのが見た目は良いですね。上の解でエラーが起きた環境・起きなかった環境両方で正常な動作を確認。

参考サイト

[fair-adjustment2]Mysql サブクエリを使ったUPDATEで引っかかる件 (エラーコード 1093)
[仕事SPOT]MySQLでサブクエリ(エラー#1093を回避する方法)
[OKWAVE]同一テーブルのデータを参照してUPDATE

この記事は役に立ちましたか?
  • _(:3」∠)_ 面白かった (0)
  • (・∀・) 参考になった (0)
  • (`・ω・´) 役に立った (0)