MySql数据库delete数据时,表不能使用别名
应用mysql数据库使用delete操作时:
delete from myschool.`result` r where r.subjectNo in (
select s2.subjectNo from myschool.subject s2 where s2.teacherID =(
select t.teacherID from myschool.teacher t where t.teacherName ='叶平')
);
报错
SQL 错误 [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
'r where r.subjectNo in (
select s2.subjectNo from myschool.subject s2 wher' at line 1
看到”SQL syntax”就知道SQL执行错误的原因是SQL语法不对,但是,这条删除的SQL横看竖看都没有问题呀,Oracle能够正常执行,为什么MySQL却不行呢?
上网查了一下原因,原来是MySQL数据库delete数据时,不支持表别名,这个问题以前也没有注意到,在此记录一下。