SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Please help me, URGENT

    Trouble
    =======================
    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
    =======================

    Purpose
    =======================
    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...

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of MySQL are you using?

    Some earlier version don't like the ... IN (SELECT ... ) construct.

    Mike

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The latest release of Mysql has supports for subqueries. So, an upgrade would solve your problem.

  4. #4
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just looking at the MySQL manual for delete:

    http://dev.mysql.com/doc/mysql/en/delete.html

    it has the following statement:

    Currently, you cannot delete from a table and select from the same table in a subquery.
    Mike

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MikeBigg
    What version of MySQL are you using?

    Some earlier version don't like the ... IN (SELECT ... ) construct.

    Mike
    Using MySQL Server 4.0.16-nt

    But, I've previously installed the latest from MySQL official site, but after that, nothing of my clients (PHPMyAdmin, MySQL Control Center, etc.) worked with it, some of them couldn't authenticate with the MySQL server & some of them just crashed! Then I reverted back to this MySQL version. Any idea?


Bookmarks

Posting Permissions

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