1

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
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.