Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unhappy Unanswered: convert MS Access DELETE statement to MySQL (was "Please help me, URGENT")

    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)"

    "tblcdr" specification
    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...
    Shahriar Kabir

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    see this other thread for an example of a delete strategy you could use | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts