Page 1 of 1

what is the query to delete the duplicate records from Table

Posted: Mon Jan 02, 2006 10:57 pm
by dkshri_jain
what is the query to delete the duplicate records from Table ?

Posted: Mon Jan 02, 2006 11:05 pm
by timvw
define duplicate... once you know which attributes (column values) that are used to determine if they are the same you could do it like:

Code: Select all

DELETE FROM table
WHERE tableid IN (
 SELECT tableid
 FROM table
 GROUP BY (attribute1, attribute2, ...)
 HAVING COUNT(*) > 1
)

Posted: Mon Jan 02, 2006 11:06 pm
by josh

Code: Select all

select count(*) as count, `record` from `table`group by `record`
in mysql that shows the count and the records, go through the resultset in php and take care of dupes


edit timvw's is better