Invalid Token in MySQL statement

Question

I am trying to delete duplicates in my MySql database keeping 1 row using the solution from this question.

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

But I keep getting an "Invalid Token" error with the DELETE alias.

What am I doing wrong?

MYSQL VERSION

Server: Localhost via UNIX socket Server type: MySQL Server version: 5.7.10 - MySQL Community Server (GPL) Protocol version: 10

Sorry it was a typo, I tried to make it a little more readable and forgot to change u1 over. — user-44651, Dec 24 '15 at 03:11
after editing to `u1` to `e1` in your IDE, does it still complain? asking that to make sure because of the image you linked. — saljuama, Dec 24 '15 at 03:24
What client are you using to connect to MySQL? The syntax seems right, and the error does not look like a proper error in the usual style of MySQL. — Michael - sqlbot, Dec 24 '15 at 14:15

score 1 · Accepted Answer · edited May 23 '17 at 12:07

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 |
+----+-------------------+---------------------+

The delete query

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
);

Results

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 |
+----+-------------------+---------------------+

Inspired from Martin Smith at https://stackoverflow.com/a/4606939/1816093

One could use rcvDate for the max instead.

score 0 · Answer 2 · answered Dec 24 '15 at 03:32

0

The correct syntax for DELETE query requires nothing between DELETE and FROM.
http://dev.mysql.com/doc/refman/5.7/en/delete.html

Your query should be in this form

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

answered Dec 24 '15 at 03:32

ekstrakt

900
1
12
28

#1064 - 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 'e1, emails e2 WHERE e1.email = e2.email AND e1.pnum > e2.pnum' at line 3 – user-44651 Dec 24 '15 at 03:32
When you're deleting from a `JOIN`, you need to specify which table you want to delete from. – Barmar Jan 04 '18 at 20:56

Invalid Token in MySQL statement

2 Answers2

The delete query

Results