【MYSQL 5.0.77】 相関クエリ内でdeleteできないこと、サブクエリと同一名のテーブルの行をメインクエリでdeleteできないこと

◎以下の操作は全て、phpMyAdmin - 2.11.11.3のSQL文発行機能をMYSQL 5.0.77に対して用いて行った。

相関クエリで行をdeleteできなかったことと、
サブクエリとメインクエリとで使っている同一名のテーブルについて、その行をメインクエリでdeleteできなかったことについて

先ず、次の相関クエリは通って結果を得ることができた。

①

select * from TMPADR t1
where t1.id > ( select min(t2.id) from TMPADR t2 where t1.number = t2.number )

しかし、これらの該当行を削除しようとする次のdelete相関クエリはエラーが発生して通らなかった。

②

delete from TMPADR t1
where t1.id > ( select min(t2.id) from TMPADR t2 where t1.number = t2.number )

相関クエリのサブクエリで用いているテーブルと同名のテーブルを、

メインクエリでdelete文の対象にしているためにエラーが生じるのかと考えて、

サブとメインとで異なるテーブルを用いる次の相関クエリを作成した。

TMPADRと、tmpadr2は全く同じ内容のテーブルである。

③

delete from TMPADR t1
where t1.id > ( select min(t2.id) from tmpadr2 t2 where t1.number = t2.number )

しかし、次のエラーが発生してdelete相関クエリは通らなかった。

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where t1.id > ( select min(t2.id) from tmpadr2 t2 where t1.number = t2.number )' at line 2 

①のクエリは通ったことから、

とりあえず、①の相関クエリの結果をWHERE句で指定し、相関クエリの外でDELETEしてみようとした。

先ず、DELETE文の代わりにSELECT文を次のように発行してみると、

クエリは通り、結果が返された。

④

SELECT * FROM TMPADR WHERE id IN (
  SELECT id FROM TMPADR t1 WHERE t1.id > (
    SELECT min(t2.id) FROM TMPADR t2 WHERE t1.number = t2.number )
)

つづいて、該当する行を削除する次のクエリを作成したが、またエラーが発生した。

⑤

DELETE FROM TMPADR WHERE id IN (
  SELECT id FROM TMPADR t1 WHERE t1.id > (
    SELECT min(t2.id) FROM TMPADR t2 WHERE t1.number = t2.number )
)

次のエラーが発生した。

#1093 - You can't specify target table 'TMPADR' for update in FROM clause 

そこで、DELETE文を発行しているメインクエリと、サブクエリである相関クエリで、同じテーブルを用いないようにしてみた。

TMPADRテーブルの複製を、tmpadr2テーブルとして作成した。

⑥

DELETE FROM TMPADR WHERE id IN (
  SELECT id FROM tmpadr2 t1 WHERE t1.id > (
    SELECT min(t2.id) FROM tmpadr2 t2 WHERE t1.number = t2.number )
)

すると、ようやく、クエリを受け付けてもらえて、該当行が削除された。

（まとめ）

MYSQL（5.0.77）+phpMyAdmin - 2.11.11.3で次のことができなかった。

・相関クエリでは、該当行を削除するdeleteを発行することができなかった。（　③のクエリ　）

・サブクエリで使っている同じ名前のテーブルの行は、メインクエリからdeleteすることができなかった。（　⑤のクエリ　）

相関クエリやサブクエリで扱われるテーブルは別名を与えたとしても、主クエリではselectすることはできるがその行をdeleteできなかったということになると思う。

これが標準的なSQLの動作なのかどうかはよくわからない。