The following SQL works in MS Access but denies in MySQL;
"DELETE FROM tblcdr WHERE tblcdr.CID IN (SELECT CID FROM tblcdr AS C WHERE C.CID = tblcdr.CID AND C.cdrID > tblcdr.cdrID)"
CDRID -> int, auto_increament
CDID -> int
I am capturing data from a hardware device that serves the data in CSV format. I then export the data right into the database. Upto this part, thigns are alright.
Now, whenever I lose connectivity with the device, it starts from the start of the data stream when I reconnect with it. It causes the duplication of thousands of data thousand times.
So I needed an SQL DELETE query that will delete the rows that are the duplicates leaving the parent (orginal) row intact.
Here the CDID column is the unique identifier supplied by the device, so for the rows with same CDID, we can call them duplicates. Please don't be confused with CDRID & CDID. CDRID is the actual IDENTITY column in the table & values for CDID is supplied by the device.
Thanks in advance...