what is the query to delete the duplicate records from Table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dkshri_jain
Forum Newbie
Posts: 8
Joined: Tue Dec 27, 2005 12:03 am
Location: India
Contact:

what is the query to delete the duplicate records from Table

Post by dkshri_jain »

what is the query to delete the duplicate records from Table ?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Post Reply