How to delete duplicate records [duplicate]

Question

I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field.

SELECT `StartTime`, COUNT(`CallDetailRecordID`) AS cnt FROM `CallDetailRecord`
GROUP BY `StartTime` HAVING cnt > 1

Sample output

 StartTime     cnt  
-------------  --------
        0        198
1340511506737         2
1340511958364         2
1340512141687         2
1340512191631         2
1340512244925         2 
1340512670902         2

Answer 1

Using a DELETE JOIN

--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime 
-- 
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord 
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
-- 
INSERT INTO CallDetailRecord_Keys 
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

Test this against a copy of the table like this:

CREATE TABLE CallDetailRecord_Test LIKE CallDetailRecord;
INSERT INTO CallDetailRecord_Test SELECT * FROM CallDetailRecord;
--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord_Test WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime 
-- 
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord_Test
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
-- 
INSERT INTO CallDetailRecord_Keys 
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord_Test WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord_Test A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

If the CallDetailRecord_Test has the rows left over that you wanted, then this works for.

Give it a Try !!!

CAVEAT

Some would recommend doing a DELETE JOIN of a table against itself. I am not comfortable with that because some keys could disappear during query optimization and the rows would not be deleted as expected. I wrote about this 2.5 years ago in this post : Problem with MySQL subquery