MySQLのUPDATE文でサブクエリ

質問

MySQLのUPDATE文でサブクエリの使い方について教えて下さい。

MySQLのUPDATE文でサブクエリを使うと「#1093 - You can't specify target table 'exam' for update in FROM clause 」というエラーになってしまいます。

「あるテーブルに対してデータを追加・更新する場合、同じテーブルをサブクエリーに使えない」
「サブクエリー内のFrom句はテンポラリテーブルとして扱うことが可能」
ということはわかりましたが、これに従いSQL文を変更してもうまくいきません。
どのようにSQL文を作ったら良いか教えていただければと思います。

環境
PHP:5.1.6
MySQL：5.0.77

------具体的なSQL文---------------------------------------

テーブル名：sample
カラム名：date（DATETIME型）,name,flag

上記テーブルに対して、

1．社員（列名：nameのsuzuki）の最新出勤日（列名：date)のflagを1にする
2．社員（列名：nameのsuzuki）の最新出勤日以外（列名：date)のflagを2にする

という処理をしたいと思います。

まず、SELECT文を作ってみました。

1．最新出勤日データを抽出
SELECT * FROM sample WHERE (date= (select max(date) from sample)) and name='sizili'

2.最新出勤日以外のデータを抽出
SELECT * FROM sample WHERE not (date= (SELECT max(date) FROM sample)) and name='suzuki'

これをUPDATE文に変更すると、
#1093 - You can't specify target table 'exam' for update in FROM clause
というエラーになります。

上記をUPDATE文に変更

1．最新出勤日データを抽出
UPDATE sample SET flag=1 WHERE (date= (select max(date) from sample)) and name='sizili'

2.最新出勤日以外のデータを抽出
UPDATE sample SET flag=2 WHERE not (date= (SELECT max(date) FROM sample)) and name='suzuki'

エラー文を検索したところ下記のような情報を見つけることができました。

「仕事SPOT」
MySQLでサブクエリ（エラー#1093を回避する方法）
http://wsjp.blogspot.com/2009/12/mysql1093.html

「あるテーブルに対してデータを追加・更新する場合、同じテーブルをサブクエリーに使えない」
「サブクエリー内のFrom句はテンポラリテーブルとして扱うことが可能」
と記載されています。

このページに掲載してあるように変更を加えてみました。

1．最新出勤日データを抽出
UPDATE sample SET flag=1 WHERE (date= (select max(date) as test_max from sample)) as text_tb and name='sizili'

2.最新出勤日以外のデータを抽出
UPDATE sample SET flag=2 WHERE not (date= (SELECT max(date) as test_max FROM sample)) as test_tb and name='suzuki'

これでもエラーになってしまい、うまくいきません。

回答No.2

23468 · ベストアンサー

掲載されてるサイトの解説をちゃんと読めば、できると思いますけど…

UPDATE sample SET flag=2 where date = (
SELECT max_date
FROM ( SELECT MAX( date ) AS max_date FROM sample WHERE name='suzuki' ) AS temp1
);

お礼

ご回答ありがとうございます。
うまく動きました。

例示したサイトを参考に自分でも作ってみましたが、うまくSQL文を作れていませんでした。
うまく動かず先に進めなかったので、とても助かりました。

回答No.1

yambejp

>1．社員（列名：nameのsuzuki）の最新出勤日（列名：date)のflagを1にする
>2．社員（列名：nameのsuzuki）の最新出勤日以外（列名：date)のflagを2にする

updateの中に同じテーブルを含んじゃいけないんだから分けるしかないでしょう

SELECT @max:=max(date) FROM sample;
UPDATE sample SET flag=(date=@max,1,2);

お礼

ご回答ありがとうございます。

UPDATEの中に同じテーブルを含むとダメだと私も思っていましたが、ネットで調べて例示したサイトに辿り着きました。

「23468」さんが回答して下さいましたが、

UPDATE sample SET flag=2 where date = (
SELECT max_date
FROM ( SELECT MAX( date ) AS max_date FROM sample WHERE name='suzuki' ) AS temp1
);

というSQL文でうまく動きました。
SQL文は奥が深いですね。

今後とも、ご回答宜しくお願いします。