Mysql query to delete duplicates based on part of timestamp

Question

I have a webshop which has gotten some duplicate orders because of paypal sending some payment notifications twice or even 3-4-5 times, with random interval between them, ranging from seconds to minutes.

Example from my orders-table:

ID     productname    buyer    timestamp
1      apples         john     2014-07-01 19:22:20
2      bananas        john     2014-07-01 19:22:20
3      oranges        mary     2014-07-01 19:22:52
4      apples         john     2014-07-01 19:22:53
5      bananas        john     2014-07-01 19:22:53
6      apples         chris    2014-07-01 19:22:54

I want to delete all duplicate rows, and by duplicate i mean that productname, buyer and date of purchase should be equal. (only date, not the time part of the timestamp)

So from the table above, this should be deleted:

4      apples         john     2014-07-01 19:22:53
5      bananas        john     2014-07-01 19:22:53

Because he bought the same things earlier the same day.

How would you go about doing this?

Instead of a date/time-based criteria which could easily be any length of time, why not compare the orders themselves? Any order with the exact same items and quantity to the same customer is a potential duplicate. Anyway, doesn't Paypal provide your order ID? — wallyk, Jul 01 '14 at 17:37
I'd be really annoyed if I were your customer that intentionally made two identical orders on the same day. — Paul McNett, Jul 01 '14 at 17:38
Without any further information, I'm gonna assume it's your side of the application that's messing up, not Paypal. Solving this problem like this seems like a really, really bad idea. — wvdz, Jul 01 '14 at 18:15
well, its paypal that send the message multiple times. they have done that maybe 2 or 3 times in the last 4 years ive been running webshops. so its their problem isnt it. i mean i could make measures to fix their error, but its their fail. — Kristian Rafteseth, Jul 01 '14 at 19:26

score 1 · Accepted Answer · answered Jul 01 '14 at 17:38

1

you can do it with EXISTS

DELETE FROM table AS t
WHERE EXISTS(
    SELECT * 
    FROM table 
    WHERE id != t.id 
        AND productname = t.productname 
        AND buyer = t.buyer 
        AND DATE(timestamp) = DATE(t.timestamp))

answered Jul 01 '14 at 17:38

chresse

5,486
3
30
47

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Here is another similar question. What it really comes down to is making the id a unique key field so that you can't insert duplicates in the first place.

edited May 23 '17 at 12:20

Community

1
1

answered Jul 01 '14 at 17:36

SyntaxTerror

346
1
10

Mysql query to delete duplicates based on part of timestamp

2 Answers2