学生表(student2) 如下:
id code name
1 2005001 张三
2 2005002 李四
3 2005001 张三 执行下面语句delete from student2 where id not in(select min(id) from student2 group by name);报错:
ERROR 1093 (HY000): You can't specify target table 'student2' for update in FROM
clause需要建立虚表来删除:delete from student2 where id not in(select mid from (select min(id) mid
from student2 group by name) as t);
这是为什么啊?我用的是mysql
id code name
1 2005001 张三
2 2005002 李四
3 2005001 张三 执行下面语句delete from student2 where id not in(select min(id) from student2 group by name);报错:
ERROR 1093 (HY000): You can't specify target table 'student2' for update in FROM
clause需要建立虚表来删除:delete from student2 where id not in(select mid from (select min(id) mid
from student2 group by name) as t);
这是为什么啊?我用的是mysql
例如,该限制适用于具有下述形式的语句: DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);例外:如果为FROM子句中更改的表使用子查询,前述禁令将不再适用。
例如: UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...)
AS _t ...);禁令在此不适用,这是因为FROM中的子查询已被具体化为临时表,因此 “t”中的相关行已在满足“t”条件的情况下、在更新时被选中。 English:· In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have a解决:依据手册将该命令改为:UPDATE forum_members AS A
INNER JOIN (
SELECT M_NAME
FROM forum_members
GROUP BY M_NAME
HAVING count( * ) >1
) AS B ON A.M_NAME = B.M_NAME
SET A.is_multi =1
delete student2 t1 where rowid>(select min(t2.rowid) from student2 t2 where t1.col2=t2.col2 and t1.col3= t2.col3 ...)
所以我说他跑错区了