mysql中[Err] 1093 - You can't specify target table 'student' for update in FROM clause错误详解

错误概述:

  关于这个错误,意思是说你不能先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
)