SQL删除问题(删除除了id号不同,其他都相同的学生冗余信息)

[jxwnhj0717]

学生表(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

[hebo2005]

你跑到ORACLE区来问MYSQL的删除问题?错误信息都是MYSQL的

[jxwnhj0717]

[Quote=引用 1 楼 hebo2005 的回复:]
你跑到ORACLE区来问MYSQL的删除问题?错误信息都是MYSQL的
[/Quote]
没看到mysql区，感觉这种错误mysql和oracal应该差不多吧

[「已注销」]

太教条化了吧，哪个区的无所谓。人家问问题，不想回答的靠边站。

[「已注销」]

不好意思，我也靠边站。最近一直都在做oracle，好久没碰mysql了，很多都忘了。

[oraclemch]

mysql对子查询的支持是比较薄弱的 ，就是说 update的where语句中不能子查询 

在MySQL手册中的错误代码:

错误：1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)

消息：不能在FROM子句中制定要更新的目标表'%s'。

 

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

[oraclemch]

呵呵，我是从网上找的，对mysql也不熟悉，不过这个网友解决了问题，估计你的也可以解决的吧~！

http://blog.sina.com.cn/s/blog_56fa1f8d01009wx9.html

[huangdh12]

delete from student2 where id not in(select min(id) from student2 group by name);


delete student2 t1 where rowid>(select min(t2.rowid) from student2 t2 where t1.col2=t2.col2 and t1.col3= t2.col3 ...)

[hebo2005]

[Quote=引用 3 楼 samoobook 的回复:]
太教条化了吧，哪个区的无所谓。人家问问题，不想回答的靠边站。
[/Quote]
他的语句在ORACLE里对的,在MYSQL里就不知道
所以我说他跑错区了