Geek asks and answers

How to remove duplicate records in sql

Possible duplicate:
How to remove duplicate rows using SQL?

I have a table with records and I want to delete all duplicate records

DELETE FROM 'table'
WHERE 'field' IN 
(
SELECT 'field' FROM 'table' GROUP BY 'field'
HAVING (COUNT('field')>1)
)

Why is this not working?

+5
source share
4 answers

Perhaps you can explore with a command DISTINCTto select only unique entries based on the field.

You can create a new table with unique records. As an example...

CREATE TABLE nonDuplicates  
SELECT DISTINCT * FROM yourTable group by field
+5
source

This gives you several results: -

SELECT field FROM `table`
GROUP BY field
HAVING (COUNT('field)>1

Try to do this:

SELECT TOP 1 field
FROM `table`
GROUP BY field 
HAVING (COUNT(field)>1
+1
source

.

SELECT field 
FROM `table`
GROUP BY field
HAVING COUNT('field') > 1

php, , ,

DELETE 
FROM `table`
WHERE field IN (your values) AND field != savedID
0

MySQL , , // ​​ .

, , ( ).

, - ( , id ):

DELETE d 
FROM table_with_duplicates d 
JOIN ( 
   SELECT min(id) as min_id, field
   FROM table_with_duplicates 
   GROUP BY field
) keep ON keep.field = d.field
      AND keep.min_id <> d.id;

(, lowes id).

( ), AND keep.min_id <> d.id.

Edit

, ( ), :

DELETE d 
FROM table_with_duplicates d 
JOIN ( 
   SELECT field
   FROM table_with_duplicates 
   GROUP BY field
   HAVING count(*) > 1
) del ON del.field = d.field;
0

More articles:


All Articles