PDA

View Full Version : MySQL DELETE SQL not working, please help

SKJoy2001
02-15-2005, 08:24 AM
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.
=======================

I am using MySQL Server 4.0.16-nt. Thanks in advance...
ReadMe.txt
02-15-2005, 02:00 PM
what version of mysql do you have? subqueries only work in 4.1 or higher.
SKJoy2001
02-16-2005, 04:48 AM
Using MySQL Server 4.0.16-nt
SKJoy2001
02-16-2005, 04:54 AM
what version of mysql do you have? subqueries only work in 4.1 or higher.
Looks like you've got me man. Thank you so much.

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?
Mhtml
02-16-2005, 06:10 AM
In the future please give your threads a relevant title.
SKJoy2001
02-16-2005, 06:19 AM
In the future please give your threads a relevant title.
like now ;)
Mhtml
02-16-2005, 06:23 AM
Much better. Although a mod will have to change the actual thread listing title.