0

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?

Improve this question
18
  • You have to distinguish one row from it's duplicate somehow. (Any id column, or timestamp? What's the primary key?) Commented Sep 21, 2015 at 11:48
  • Also, don't tag products not involved. Are you using MySQL or MS SQL Server? Commented Sep 21, 2015 at 11:48
  • 1
    possible duplicate of How to delete duplicate rows in sql server? Commented Sep 21, 2015 at 11:48
  • What all are the columns in the table article_numbers? Commented Sep 21, 2015 at 11:49
  • Each row has an ID field with a different ID for each row. Commented Sep 21, 2015 at 11:49

6 Answers 6

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

2 Comments

OP needs mysql query
This wasn't apparent at the time of my posting.
0 
Delete c from (select *,rank() over(order by article_number) as r from article_numbers )c where c.r!=1
Improve this answer

Comments

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.

Improve this answer

2 Comments

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
Sorry, apparently MySQL doesn't support it. (Conforms to Core SQL-2003.)
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
Improve this answer

2 Comments

OP needs mysql query.
oh this is for SQL Server. can be converted to MYSQL.
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.