mysql项目 -- delete BUG

项目场景：

mysql 客户端

表的增删改查时 设置嵌套筛选条件

---

问题描述

删除student中没有考试成绩的学生记录，请使用not in；


“这里97003 c语言 60 这一行数据 的code应该为97003”

---

实现过程

命令：mysql> delete *from student where not in (select code from subject);
结果：ERROR 1064 (42000): You have an error in your SQL syntax;\
 check the manual that corresponds to your MySQL server version for \
 the right syntax to use near '*from student where not in (select code\
 from subject)' at line 1

格式错误：
(修改)

命令：delete from student where code = （\
select code from student where code not in \
(select code from subject));
结果：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)

————————————————

原文链接：https://blog.csdn.net/qq_34698318/article/details/70261486

（修改）

命令：mysql> delete from student where code = (select code from (select \
*from student) where code not in (select code from subject));

结果：ERROR 1248 (42000): Every derived table must have its own alias

错误原因：
select * from 表名，此为标准格式
而以上的查询语句，"表名"的位置其实是一个结果集，mysql并不能识别
此时给结果集设置一个别名，并且以select a.编号,姓名 from a;的方式查询，将此结果集视为一张表就可以正常查询出数据了
————————————————
原文链接：http://t.csdnimg.cn/eVLLN

命令：mysql> delete from student where code = (select code from (select *from student)a  where code not in (select code from subject));
结果：Query OK, 1 row affected (0.38 sec)

+-------+--------+------+
| code  | name   | age  |
+-------+--------+------+
| 97001 | 张三   |   22 |
| 97002 | 赵四   |   21 |
| 97004 | 李五   |   21 |
+-------+--------+------+
3 rows in set (0.01 sec)

---