这样写正常:
------------------
SELECT id FROM tree WHERE father!=0 AND father NOT IN (SELECT id FROM tree );这样写出错:
------------------
DELETE FROM tree WHERE father!=0 AND father NOT IN (SELECT id FROM tree );ERROR 1093 (HY000): You can't specify target table 'tree ' for update in FROM clause
------------------
SELECT id FROM tree WHERE father!=0 AND father NOT IN (SELECT id FROM tree );这样写出错:
------------------
DELETE FROM tree WHERE father!=0 AND father NOT IN (SELECT id FROM tree );ERROR 1093 (HY000): You can't specify target table 'tree ' for update in FROM clause
DELETE FROM tree WHERE father!=0 AND father NOT IN (SELECT id FROM tree );
这条SQL如果能执行的话,你考虑下下面这种情况:+------+--------+
| id | father |
+------+--------+
| 1 | 3 |
| 2 | 1 |
+------+--------+显然,id=1的数据会被删除,但是它被删除后id=2的数据删不删?
================================这个写法在 m$sql中可以执行的.
显然,id=1的数据会被删除,但是它被删除后id=2的数据删不删?
----------------
应该不删我的理解是:
首先 执行 SELECT id FROM tree 返回数据为 1 和 2
然后 执行 DELETE FROM tree WHERE father!=0 AND father NOT IN ( 1,2 )
| id | father |
+------+--------+
| 1 | 3 |
| 2 | 1 |
+------+--------+如果id=2的数据不删,那么跟你的SQL初衷是违背的,因为它的father不在id列表中
这样的sql在什么数据库会执行?
都应该不会执行的。
单表语法:DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
多表语法:DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
或:DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_definition]