MySQL语法相关问题一

-纯碎个人记录，非喜勿喷

MySQL DELETE语句起别名报错问题

问题一

刷Leetcode的时侯写了这样一个SQL:

DELETE from person p0 where p0.id in(

SELECT p1.id from person p1,person p2
where 
p1.email = p2.email
and p1.id > p2.id
)

Error : 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 'p0 where p0.id in(

SELECT p1.id from person p1,person p2
where 
p1.email = p2.e' at line 1

解决：

mySql 的Delete语句在使用别名时，与其他语句语法有些许不一致，除了在表名后边加上别名外，还需要在Delete关键字后边也加上别名。注意：两处别名需要一致

新SQL

DELETE p from person p where p.id in(

SELECT p1.id from person p1,person p2
where 
p1.email = p2.email
and p1.id > p2.id

)

问题二

报错

Error : You can't specify target table 'p' for update in FROM clause

You can’t specify target table ‘表名’ for update in FROM clause

翻译为：不能先select出同一表中的某些值，再update这个表(在同一语句中)

正确的写法

DELETE p from person p where p.id in(

select a.id from (
SELECT p1.id from person p1,person p2
where 
p1.email = p2.email
and p1.id > p2.id
)as a

)

格式

也就是说：把结果集当作一个表，自我查询一遍

格式为：SELECT a.StudentResult FROM

            (结果集)a

删除成功

参考

MySQL Delete语句不能用别名问题
https://blog.csdn.net/hy_coming/article/details/113122781

Mysql DELETE 不能使用别名? 是我不会用！
https://blog.csdn.net/mingjia1987/article/details/79741113

mysql出现“ You can’t specify target table ‘表名’ for update in FROM clause”解决方法
https://blog.csdn.net/PoetsSociety/article/details/82391523