CODEWITHSUNDEEP
mysqlsql
razer
razer

Reputation: 61

Delete duplicates in mySQL table

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?

Upvotes: 0

Views: 721

Answers (6)

Ullas
Ullas

Reputation: 11556

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

Upvotes: 2

razer
razer

Reputation: 61

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.

Upvotes: 0

ProblemSolver
ProblemSolver

Reputation: 644

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

Upvotes: 0

jarlh
jarlh

Reputation: 44795

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.

Upvotes: 0

koushik veldanda
koushik veldanda

Reputation: 1107

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

Upvotes: 0

John Bell
John Bell

Reputation: 2350

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

Upvotes: 0

Related Questions