Description:
http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#derived-table-optimizati...
In this manual, it says,"
For statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that prior to MySQL 5.7.6 was materialized can result in an ER_UPDATE_TABLE_USED error:
mysql> DELETE FROM t1
-> WHERE id IN (SELECT id
-> FROM (SELECT t1.id
-> FROM t1 INNER JOIN t2 USING (id)
-> WHERE t2.status = 0) AS t);
ERROR 1093 (HY000): You can't specify target table 't1'
for update in FROM clause
"
But the modification based on Bug#79333, the result is not true for MySQL 5.7.11 or later. It's not affected setting of derived_merge.
MySQL5.7.6-5.7.10
mysql> DELETE FROM t1 WHERE id IN (SELECT id FROM (SELECT t1.id FROM t1 INNER JOIN t2 USING (id) WHERE t2.status = 0) AS t);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
MySQL 5.6 or earlier, MySQL5.7.11-5.7.13
mysql> DELETE FROM t1 WHERE id IN (SELECT id FROM (SELECT t1.id FROM t1 INNER JOIN t2 USING (id) WHERE t2.status = 0) AS t);
Query OK, 3 rows affected (0.00 sec)
So we need to update about manual desciption about this query.
How to repeat:
run these queries on MySQL 5.7.11 or later.
drop table if exists t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
drop table if exists t2;
CREATE TABLE t2 (id INT,status INT);
INSERT INTO t2 VALUES(1,1),(2,1),(3,0),(4,0),(5,0);
commit;
DELETE FROM t1 WHERE id IN (SELECT id FROM (SELECT t1.id FROM t1 INNER JOIN t2 USING (id) WHERE t2.status = 0) AS t);
Suggested fix:
Ask the developper for good example to explain derived_merge & update/delete.
