Delete the second record where a user has two records I have a simple database scheme, which due to previously being badly set up has numerous duplicate records.

record_table

|--------------------------------------------------------|
| id  | subid  | catid  | start_date | end_date          |
|--------------------------------------------------------|
| 1   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 2   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 3   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 4   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 5   |  3     | 1      | 2015-01-01 | 2015-12-31        |
|--------------------------------------------------------|


I want to delete records 2 and 4, because there is already a record for that subid/catid combo that covers the same dates.

I want to be able to do:

DELETE from record_table WHERE catid='1' AND 
    start_date&lt;='2015-02-13' AND 
    end_date&gt;='2015-02-13' AND 
    [the user has two records for the previous conditions]
Solution


Create a TempTable(record_table_keep) with minimum id for every subid,catid combination
Perform a DELETE JOIN between record_table and record_table_keep using LEFT JOIN
Only delete records from record_table whose id is not in record_table_keep


Proposed Query

CREATE TABLE record_table_keep
SELECT id,subid,catid FROM record_table WHERE 0=1;
ALTER TABLE record_table_keep ADD PRIMARY KEY (id);
INSERT INTO record_table_keep (id,subid,catid)
SELECT MIN(id) id,subid,catid FROM record_table GROUP BY subid,catid;
DELETE A.* FROM record_table A
LEFT JOIN record_table_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE record_table_keep;


Give it a Try !!!