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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.