remove duplicate records - mysql

Question

I want remove duplicate record & use this query:

 DELETE FROM news e
 GROUP BY e.itemId, e.tag_id
 HAVING COUNT(e.itemId) > 1
    AND COUNT(e.tag_id) > 1

but get this error:

[Err] 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 'e

How can I do this?

Well, the error message is pretty precise: `e` does not make sense there. Renaming of tables only makes sense inside SELECT and UPDATE queries. A DELETE query only requires a table name. — arkascha, Dec 18 '15 at 08:13
Possible duplicate of [MYSQL delete all results having count(\*)=1](http://stackoverflow.com/questions/1902014/mysql-delete-all-results-having-count-1) — HoneyBadger, Dec 18 '15 at 08:14

score 0 · Answer 1 · answered Dec 18 '15 at 08:24

0

I'm not sure what you're trying to acheive here, please expand your explanation to add a little more info. From what i see you actually need to create a sub query as you can't use GROUP BY directly on the delete, try something like this:

delete from table 
where columnA in (
  select columnA
  from (
      select columnA
      from YourTable
      group by columnA
      having count(*) > 1
      ) t  
)

Not exactly tailored to your problem but you should get the idea.

answered Dec 18 '15 at 08:24

Dan Hall

1,474
2
18
43

when using this format, show this error You can't specify target table 'news' for update in FROM clause – Farhad Dec 18 '15 at 08:32
Not your code it 's copy paste from http://stackoverflow.com/questions/6296102/mysql-delete-with-group-by – Dec 18 '15 at 09:06
Yes, but is that not ok? It's just helpful info and linking out of the question just causes hassle. – Dan Hall Dec 18 '15 at 09:37

score 0 · Accepted Answer · answered Dec 18 '15 at 08:43

0

for remove duplicate records, use this query

DELETE
    n1
FROM
    news n1,
    news n2
WHERE
    n1.id < n2.id
AND n1.itemId = n2.itemId
AND n1.tag_id = n2.tag_id
AND n1.tag_id IS NOT NULL

answered Dec 18 '15 at 08:43

Farhad

104
3
17

remove duplicate records - mysql

2 Answers2