Delete duplicate rows with same text

Question

I have table news with this fields:

idNews idArea title text date

What I want to do is delete all duplicate rows with same title and text except one (the one with the earliest date) i already try some query without success.

I tried these queries, but they didn't work:

DELETE FROM news WHERE idNews NOT IN (SELECT MIN(date) FROM news GROUP BY title,text, date); 

DELETE idNews FROM news WHERE date< date AND title= title and text=text;

duplicate: http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Daimos, Jul 27 '15 at 11:58
i already try edit database and use unique but i get an error ERROR 1071: Specified key was too long; max key length is 767 bytes — Daggra, Jul 27 '15 at 12:02
DELETE FROM news WHERE idNews NOT IN ( SELECT MIN(date) FROM news GROUP BY title,text, date) ; DELETE idNews FROM news WHERE date< date AND title= title and text=text — Daggra, Jul 27 '15 at 12:11

jpw · Accepted Answer · 2015-07-27T12:24:45.077

0

Assuming idNews is a key then this should work:

delete from news 
where idnews not in (
  select idnews from (
    select idnews from news
    join (
      select title, text, min(date) as min_date 
      from news
      group by title, text
    ) x 
     on news.title = x.title
    and news.text  = x.text 
    and news.date  = x.min_date
  ) a
);

The reason for the nested joins is that MySQL won't let you delete data from a table that you're directly referencing in a join. The second level subquery creates a temporary result set that will allow the delete.

Sample SQL Fiddle

edited Jul 27 '15 at 12:24

answered Jul 27 '15 at 12:15

jpw

44,361
6
66
86

Error Code: 1054. Unknown column 'x.date' in 'on clause' – Daggra Jul 27 '15 at 12:19
@Daggra That's odd, it worked fine when I tried it. I've made a small change, please try now. – jpw Jul 27 '15 at 12:25
i just change news.date > x.min_date to get the result that i want but that works :) ty for the help!! – Daggra Jul 27 '15 at 12:39

score -1 · Answer 2 · answered Jul 27 '15 at 12:04

-1

One of the methods is

delete from table as t1 inner join
(
select title,text,min(date) as date from table group by title,text
) as t2 on t1.title=t2.title and t1.text=t2.text 
where t1.date>t2.date;

answered Jul 27 '15 at 12:04

Madhivanan

13,470
1
24
29

he is expecting an identifier from table – Daggra Jul 27 '15 at 12:19

score -1 · Answer 3 · answered Jul 27 '15 at 12:09

-1

select * from news where title in (
    select title from news group by title having count(*) > 1
)

answered Jul 27 '15 at 12:09

code19

75
4

It will not delete duplicate records. Go through the questions properly. – Sabyasachi Mishra Jul 27 '15 at 12:11
he can check first if any duplications – code19 Jul 27 '15 at 12:13

Delete duplicate rows with same text

3 Answers3