错误概述:
关于这个错误,意思是说你不能先select出同一表中的某些值，再对这个表(在同一语句中)进行其他的操作(update,delete等).
因为有这种操作可能会一系列的错误[猜测可能会是容易引发递归之类的错误],但oracle等其他数据库并不会有这种问题
最简单的例子:
比如一个学生表,有id,name两个字段,如果你在去更新一个学生的姓名时候,写出如下的语句
update student set name = ‘张三’ where name in
(select name from student a where a.id=2)
这个时候则会引发当前的错误信息,应该改成如下语句
update student set name = ‘张三’ where name in
(select name from
(select * from student)
a where a.id=2)
那你看到这里可能会说这个列子写的并不好,我直接id=2的时候就好了,的确是这样,但也正如上面所说,这是个最简单的列子
再比如删除数据库中重复的数据,并且保留一条
delete
from
student
where
–查询重复的记录
name in (
select name from student
GROUP BY name
HAVING count(name) > 1
)
–保留重复记录中最小的id
and id not in (
SELECT min(id)
from student
GROUP BY
name
HAVING
COUNT(name) > 1
)
想这样操作语句,也会引发当前这个错误,应改成
delete
from
student
where
–查询重复的记录
name in (
select name from (select * from student)
GROUP BY name
HAVING count(name) > 1
)
–保留重复记录中最小的id
and id not in (
SELECT min(id)
from (select * from student)
GROUP BY
name
HAVING
COUNT(name) > 1
)