mysql - MySQL 语句中的无效 token

Question

我正在尝试使用此 question 中的解决方案删除 MySql 数据库中的重复项，保留 1 行.

DELETE
  e1
FROM
  email e1,
  email e2
WHERE
  e1.email = e2.email AND e1.pnum > e2.pnum

但我不断收到 DELETE 别名的“无效 token ”错误。

我做错了什么？

MYSQL版本

服务器:通过 UNIX 套接字的本地主机服务器类型:MySQL服务器版本:5.7.10 - MySQL 社区服务器 (GPL)协议(protocol)版本:10

Answer 1

create table email
(   -- the columns here aren't great, but illustrative
    id int auto_increment primary key,
    email text not null,
    rcvDate datetime not null
);

-- truncate table email;
insert email(email,rcvDate) values
('this is an email','2015-12-01 08:00:01'),
('greetings email','2015-12-01 09:00:01'),
('this is an email','2015-12-01 10:00:01'),
('this is an email','2015-12-01 11:00:01'),
('yet another email','2015-12-01 12:00:01');

select * from email;
+----+-------------------+---------------------+
| id | email             | rcvDate             |
+----+-------------------+---------------------+
|  1 | this is an email  | 2015-12-01 08:00:01 |
|  2 | greetings email   | 2015-12-01 09:00:01 |
|  3 | this is an email  | 2015-12-01 10:00:01 |
|  4 | this is an email  | 2015-12-01 11:00:01 |
|  5 | yet another email | 2015-12-01 12:00:01 |
+----+-------------------+---------------------+

删除查询

delete from  email
where concat(email, id) not in 
(   select dummy 
    from
    (   select concat(email, max(id)) as dummy
        from email
        group by email
    ) xDerived
);

结果

select * from email;
+----+-------------------+---------------------+
| id | email             | rcvDate             |
+----+-------------------+---------------------+
|  2 | greetings email   | 2015-12-01 09:00:01 |
|  4 | this is an email  | 2015-12-01 11:00:01 |
|  5 | yet another email | 2015-12-01 12:00:01 |
+----+-------------------+---------------------+

灵感来自 Martin Smith https://stackoverflow.com/a/4606939/1816093

可以使用 rcvDate 作为最大值。