MySQL Query: Delete duplicate entries based on date, keep oldest record from set of duplicates

Question

Ok.. So I have looked around at numerous topics on this subject and I have tried many queries to get the result I want, so as you've probably guessed I still haven't found the solution.

I have a client who regularly uploads information to a MySQL database relating to invoices. There are 3 columns in the table:

|------
|Column|Type|Null|Default
|------
|//**invoice**//|int(11)|No|0
|//**barcode**//|int(11)|No|
|//**invoice_date**//|datetime|No|

So you can see there is invoice, barcode and invoice_date. Each barcode is UNIQUE but you can have multiple barcodes associated to an invoice ID.

Now you're probably thinking: "Why didn't you just add a UNIQUE index on the barcode and invoice_date columns?" - I do have indexes on these columns.. Now you see, this is where the massive ball ache comes in to play. They use a scanner to scan the barcodes and this is then put into a CSV. Sometimes this messes up, so there will be a record that is EXACTLY the same but the invoice_date reference will be a couple of seconds or minutes different so MySQL will not interpret this as a duplicate even though it is the same.

Example of duped records:

|24815|86632|2008-08-21 10:22:50
|24899|86632|2008-09-04 17:12:30
|55555|86632|2008-08-21 10:34:41

So what I need to do is:

DELETE all records EXCEPT the record with the OLDEST invoice_date where the barcode is the same.

So from the above data set I would need to keep:

|24815|86632|2008-08-21 10:22:50 as thats the oldest record.

I've tried many queries.

E.g

DELETE I1 FROM v3_invoices_test I1
LEFT JOIN
(
    SELECT MIN(invoice_date) AS OLDESTRECORD, barcode
    FROM   v3_invoices_test
) I2 
ON I1.barcode = I2.barcode
WHERE OLDESTRECORD > I1.invoice_date

DELETE FROM v3_invoices_test
WHERE (barcode, invoice_date) IN (
    SELECT 
    barcode, 
    invoice_date
    FROM 
        v3_invoices_test I1
    WHERE 
    EXIST (
        SELECT *
        FROM v3_invoices_test I2
        WHERE I1.barcode = I2.barcode
        AND I1.invoice_date < I2.invoice_date
    )
)

If anyone could help it would be deeply appreciated!

Thanks

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

Here is your fiddle: http://sqlfiddle.com/#!2/29375b/1

So your query would be:

DELETE FROM v3_invoices_test WHERE invoice NOT IN (
  SELECT invoice FROM (
    SELECT invoice FROM v3_invoices_test JOIN (
      SELECT barcode, MIN(invoice_date) m FROM v3_invoices_test GROUP BY barcode
    ) temp ON t.barcode = temp.barcode AND t.invoice_date = temp.m
  ) a
);

Sadly, the first subquery is needed because of #1093 (https://stackoverflow.com/a/14302701/1767861). Basically, the query retrieves oldest dates grouped by the barcode, and apply a deletion on all the other ids.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 16 '14 at 11:37

Thomas Ruiz

3,431
2
17
32

Thanks for the answer, however I get this MySQL Error when running that query: #1054 - Unknown column 't.barcode' in 'on clause' – Nathan Fitzgerald - Fitzgenius Oct 16 '14 at 11:40
It was missing the "t" after the second SELECT.. Also it just freezes my server now. Probably should have mentioned there are 360,000+ records in this table. – Nathan Fitzgerald - Fitzgenius Oct 16 '14 at 14:02
Yeah it must use quite a lot of resources. Hope you managed to get through it – Thomas Ruiz Oct 17 '14 at 01:40
Yes was never going to work... Then I had a lightbulb moment and decided to do a SELECT + INSERT based on the same logic and it worked perfectly! Thanks for your help. – Nathan Fitzgerald - Fitzgenius Oct 20 '14 at 14:29

score 0 · Answer 2 · edited Aug 05 '19 at 16:10

QUERY:

DELETE t1 
FROM v3_invoices_test AS t1 
JOIN v3_invoices_test as t2 
WHERE t1.id > t2.id 
AND t1.column_name1 = t2.column_name1;

Here you need id column which is just like auto_increment column and column_name in above query means based on which column you deleting the data you can give (t1.column_name1 = t2.column_name1) and if you deleting data based more than one column you can just add (and t1.column_name2 = t2.column_name2) this to your query

and finally you need to delete the data based on oldest or latest you just change < or > in your query for oldest you can give like this (t1.id > t2.id), for newest you can give like this (t1.id < t2.id)

MySQL Query: Delete duplicate entries based on date, keep oldest record from set of duplicates

2 Answers2