How to delete some duplicated rows

Question

I'm trying to delete some duplicated rows of my table. I'm starting by select all the duplicated rows by executing this query :

SELECT a.id as id FROM table A join table B on A.site = B.site 
where A.nb_affichages = B.nb_affichages and A.nb_clics = A.nb_clics

Then i try to delete all the selected ids from the first query :

DELETE FROM table WHERE id IN ( SELECT * FROM ( SELECT a.id as id FROM table A join table B on A.site = B.site 
where A.nb_affichages = B.nb_affichages and A.nb_clics = A.nb_clics) AS p )

This second query deletes all the rows selected in the first, instead I want to let one row and delete the others.

So anyone can help me to optimize this query.

score 2 · Accepted Answer · edited Jan 20 '16 at 17:09

2

It will keep first data of duplicate value and will delete all rest values of duplicated values.

DELETE c1 
FROM table c1, table c2 
WHERE c1.nb_affichages = c2.nb_affichages 
AND c1.nb_clics = c2.nb_clics 
AND c1.site = c2.site
AND c1.id > c2.id;

edited Jan 20 '16 at 17:09

Nick

9,735
7
59
89

answered Jan 20 '16 at 16:57

Nana Partykar

10,556
10
48
77

1

Why condition `A.site = B.site` was removed? – Nick Jan 20 '16 at 17:04
1

I didn't saw that condition. I will edit @mnv. Thanks for pointing out. – Nana Partykar Jan 20 '16 at 17:07

How to delete some duplicated rows

1 Answers1

Linked