英文:
Delete statement - incorrect syntax near p1
问题
我正在尝试解决来自 leetcode 的这个问题。
我编写了以下查询：
delete from person p1
where not exists
(
select p2.email, min(p2.id)
from person p2
group by p2.email
having min(p2.id) = p1.id
)
但我遇到了错误：
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'p1'. (102) (SQLExecDirectW)
为什么会出现这个错误？
英文:
I am trying to solve this question from leetcode.
I wrote my query as below:
delete from person p1
where not exists
(
select p2.email,min(p2.id)
from person p2
group by p2.email
having min(p2.id)=p1.id
)
But I am getting the error:
>[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'p1'. (102) (SQLExecDirectW)
Why am I getting this error?
答案1
得分: 2
错误消息非常明确（错误语法），你不必为要删除的表创建别名。
delete from person
where not exists
(
select p2.email, min(p2.id)
from person p2
group by p2.email
having min(p2.id) = person.id
)
英文:
the error message was very clear(error syntax), you don't have to make alias for table you want to delete.
delete from person
where not exists
(
select p2.email,min(p2.id)
from person p2
group by p2.email
having min(p2.id)=person.id
)
答案2
得分: 1
这是另一种方法来执行操作：
从 person 表中删除
其中 id 在 (
选择 max(id) as id
从 person 表中
按 email 分组
有 count(1) > 1
);
首先，您需要通过 group by 和 having count > 1 获取重复的电子邮件，然后从该列表中获取 max(id)（最后添加的电子邮件），然后将其移除。
英文:
This is an other way to do it :
delete from person
where id in (
select max(id) as id
from person
group by email
having count(1) > 1
);
You first need to get the duplicated email by a group by and having count > 1, from this list we get the max(id) (the last added email)
Then we remove it.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论