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

Quote:

Originally Posted by ReadMe.txt

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

Quote:

Originally Posted by Mhtml

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.

02-15-2005, 08:24 AM	PM User \| #1
SKJoy2001 New to the CF scene Join Date: Feb 2005 Posts: 5 Thanks: 0 Thanked 0 Times in 0 Posts	MySQL DELETE SQL not working, please help 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... Last edited by SKJoy2001; 02-16-2005 at 06:19 AM..

02-15-2005, 02:00 PM	PM User \| #2
ReadMe.txt Regular Coder Join Date: Jun 2002 Location: Sheffield, UK Posts: 552 Thanks: 0 Thanked 0 Times in 0 Posts	what version of mysql do you have? subqueries only work in 4.1 or higher. __________________ "To be successful in IT you don't need to know everything - just where to find it in under 30 seconds" (Me Me Me Me Me Me Me Me Me)

02-16-2005, 06:10 AM	PM User \| #5
Mhtml Senior Coder Join Date: Jun 2002 Location: Sydney, Australia Posts: 3,531 Thanks: 0 Thanked 1 Time in 1 Post	In the future please give your threads a relevant title. __________________ Omnis mico antequam dominus Spookster!

02-16-2005, 06:23 AM	PM User \| #7
Mhtml Senior Coder Join Date: Jun 2002 Location: Sydney, Australia Posts: 3,531 Thanks: 0 Thanked 1 Time in 1 Post	Much better. Although a mod will have to change the actual thread listing title. __________________ Omnis mico antequam dominus Spookster!

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

02-16-2005, 04:48 AM	PM User \| #3
SKJoy2001 New to the CF scene Join Date: Feb 2005 Posts: 5 Thanks: 0 Thanked 0 Times in 0 Posts	Using MySQL Server 4.0.16-nt