这算是MySQL的一个坑哈,没法改变就去适应。这是以前写的,没在博客上的文章,文章的DELETE和此处是一样的道理。 最近一位好友问了我一个问题,就是MySQL的Delete语句删除会报ERROR1093 (HY000)错误。SQL语句如下:DELETE FROM test WHEREid=(SELECT max(id) FROM test); 我看了下这个SQL,语法上面没有什么问题啊,这不是标准SQL吗。于是我在Linux环境下测试了MySQL5.1版本和MySQL 5.5版本,均有这样的问题。后来我又到Oracle 11G R2环境下模拟了相同的问题,却可以正确地删除。后来才知道,使用MySQL进行DELETE FROM操作时,若子查询的 FROM 字句和更新或者删除对象使用同一张表,会出现错误。这里有一个变通的解决办法,可以通过多加一层SELECT别名表来变通解决。比如DELETE FROM test WHERE id=(SELECT max(id) FROM (SELECT * FROM test) AS t),但是这样的效率是极低的。这也算是MySQL的一个坑吧。
以下是操作日志:mysql> SELECT max(id) FROM test; +---------+ | max(id) | +---------+ | 49134 | +---------+ 1 row in set (0.00 sec)
mysql> DELETE FROM test WHERE id=(SELECT max(id) FROM test); ERROR 1093 (HY000): You can'tspecify target table 'test' for update in FROM clause mysql> DELETE FROM test WHERE id=(SELECT max(id) FROM (SELECT * FROM test) AS t); Query OK, 1 row affected (0.12sec)
mysql> SELECT max(id) FROM test; +---------+ | max(id) | +---------+ | 49133 | +---------+ 1 row in set (0.00 sec)
同理,UPDATE类似。 mysql> UPDATE test SET name='LARRY' WHERE id=(SELECT max(id) FROM test); ERROR 1093 (HY000): You can'tspecify target table 'test' for update in FROM clause mysql> UPDATE test SET name='LARRY' WHERE id=(SELECT max(id) FROM (SELECT * FROM test) AS t); Query OK, 1 row affected (0.16sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT id,name FROM test WHERE id=(SELECT max(id) FROM test); +-------+-------+ | id | name | +-------+-------+ | 49133 | LARRY | +-------+-------+ 1 row in set (0.03 sec)
WHERE
user_id NOT IN(select user_id from (SELECT user_id FROM userInf LIMIT 0,8)as t)
谢谢版主 !
-----------
另附:版主,我这里有一个J2EE的问题,不知可否指点下?http://bbs.csdn.net/topics/390708216
-----------
另附:朋友,我这里有一个J2EE的问题,不知可否指点下?http://bbs.csdn.net/topics/390708216
以下是操作日志:mysql> SELECT max(id) FROM test;
+---------+
| max(id) |
+---------+
| 49134 |
+---------+
1 row in set (0.00 sec)
mysql> DELETE FROM test WHERE id=(SELECT max(id) FROM test);
ERROR 1093 (HY000): You can'tspecify target table 'test' for update in FROM clause
mysql> DELETE FROM test WHERE id=(SELECT max(id) FROM (SELECT * FROM test) AS t);
Query OK, 1 row affected (0.12sec)
mysql> SELECT max(id) FROM test;
+---------+
| max(id) |
+---------+
| 49133 |
+---------+
1 row in set (0.00 sec)
同理,UPDATE类似。
mysql> UPDATE test SET name='LARRY' WHERE id=(SELECT max(id) FROM test);
ERROR 1093 (HY000): You can'tspecify target table 'test' for update in FROM clause
mysql> UPDATE test SET name='LARRY' WHERE id=(SELECT max(id) FROM (SELECT * FROM
test) AS t);
Query OK, 1 row affected (0.16sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT id,name FROM test WHERE id=(SELECT max(id) FROM test);
+-------+-------+
| id | name |
+-------+-------+
| 49133 | LARRY |
+-------+-------+
1 row in set (0.03 sec)