Delete duplicates in mySQL table

Question

I am trying to write my first mySQL query. I need to delete rows if they have the same article-number field. I wrote this query:

    SELECT
    article_number, COUNT(*)
FROM
    article_numbers
GROUP BY
    article_number
HAVING 
    COUNT(*) > 1

It shows me all the rows that are duplicate. But how can I delete all but 1 for each duplicate?

Thanks

EDIT:

I tried this query:

delete article_numbers from article_numbers inner join 
    (select  article_number
     from article_numbers 
     group by article_number
     having count(1) > 1) as duplicates
   on (duplicates.article_number = article_numbers.article_number)

but it gives me this error:

Cannot delete or update a parent row: a foreign key constraint fails (api.products, CONSTRAINT products_article_number_id_foreign FOREIGN KEY (article_number_id) REFERENCES article_numbers (id))

EDIT 2:

I disabled the foreign key temporarily, and now my delete query works. But how can I modify it that one of the duplicate rows is not deleted?

You have to distinguish one row from it's duplicate somehow. (Any id column, or timestamp? What's the primary key?) — jarlh, Sep 21 '15 at 11:48
Also, don't tag products not involved. Are you using MySQL or MS SQL Server? — jarlh, Sep 21 '15 at 11:48
possible duplicate of [How to delete duplicate rows in sql server?](http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Evaldas Buinauskas, Sep 21 '15 at 11:48
Another question is, do you simply want to remove duplicates now, or do you also want to prevent them from ever happening? — Chris, Sep 21 '15 at 11:52
Your *first* SQL query is to delete rows. That seems curious . . . how did the data get there in the first place? ;) — Gordon Linoff, Sep 21 '15 at 11:53
asking again `What all are the columns in the table article_numbers?` — Ullas, Sep 21 '15 at 11:54
@Ullas there are the following columns: id, article_number, created_at, updated_at, price — razer, Sep 21 '15 at 11:55
Did you try to disable the foreign key temporarily? http://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql — xpy, Sep 21 '15 at 12:02
@xpy thanks, now my delete query works. How can I modify it though, that it keeps one of the duplicates, meaning if there are 3 rows with the same article_number, 1 stays? — razer, Sep 21 '15 at 12:10

Ullas · Answer 1 · 2015-09-21T12:24:42.537

2

Use a CROSS JOIN.

Query

delete t1
from article_numbers t1,
article_numbers t2
where t1.id > t2.id 
and t1.article_number = t2.article_number;

Fiddle demo

edited Sep 21 '15 at 12:24

answered Sep 21 '15 at 12:05

Ullas

11,450
4
33
50

score 0 · Answer 2 · answered Sep 21 '15 at 11:52

0

I use a rather simple query to remove dupes:

;WITH DEDUPE AS (
SELECT ROW_NUMBER() OVER(
    PARTITION BY article_number
        ORDER BY (SELECT 1)) AS RN
FROM article_numbers)
DELETE FROM DEDUPE
WHERE RN != 1

answered Sep 21 '15 at 11:52

John Bell

2,350
1
14
23

3

OP needs `mysql` query – Ullas Sep 21 '15 at 11:52
This wasn't apparent at the time of my posting. – John Bell Sep 21 '15 at 13:39

score 0 · Answer 3 · answered Sep 21 '15 at 11:54

0

Delete c
from (select *,rank() over(order by article_number) as r  from article_numbers )c
where c.r!=1

answered Sep 21 '15 at 11:54

koushik veldanda

1,079
10
23

score 0 · Answer 4 · answered Sep 21 '15 at 11:54

0

Delete a row if same article_number but higher id exists:

delete from article_numbers t1
where exists (select 1 from article_numbers t2
              where t2.article_number = t1.article_number
                and t2.id > t1.id)

Core ANSI SQL, so I suppose it works with both MySQL and SQL Server.

answered Sep 21 '15 at 11:54

jarlh

42,561
8
45
63

`You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1 where exists (select 1 from article_numbers t2 where t2.article' at line 1` – razer Sep 21 '15 at 11:59
Sorry, apparently MySQL doesn't support it. (Conforms to Core SQL-2003.) – jarlh Sep 21 '15 at 12:04

score 0 · Answer 5 · answered Sep 21 '15 at 11:59

0

I think this would help:

    WITH tblTemp as
    (
    SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
       As RowNumber,* FROM <table_name>
    )
    DELETE FROM tblTemp where RowNumber >1

answered Sep 21 '15 at 11:59

ProblemSolver

636
1
8
16

OP needs mysql query. – Ullas Sep 21 '15 at 12:00
oh this is for SQL Server. can be converted to MYSQL. – ProblemSolver Sep 21 '15 at 12:01

score 0 · Answer 6 · answered Sep 21 '15 at 12:23

I modified my query and I think it works now:

SET FOREIGN_KEY_CHECKS=0;
delete article_numbers from article_numbers inner join 
    (select  min(id) minid, article_number
     from article_numbers 
     group by article_number
     having count(1) > 1) as duplicates
   on (duplicates.article_number = article_numbers.article_number and duplicates.minid <> article_numbers.id)

But it seems very complex. I will check @Ullas method to see if it works, too.

Delete duplicates in mySQL table

6 Answers6