If I remember correctly, and this would have been on MSSQL, you can use
joins when deleting

It's something like 
        
        DELETE FROM table1, table2
        WHERE some clause to join goes here.

Have a play about with it see what you get.

Ade
        

-----Original Message-----
From: Stephen Moretti [mailto:[EMAIL PROTECTED]
Sent: 24 July 2003 11:06
To: CFDeveloper Dev List
Subject: [ cf-dev ] mySQL Delete problem....


Hmmm...

Run into a spot of bother with mySQL..... 3.23.56 (Basically not version 4)

I need to delete a bunch of rows from several related tables...

I have

tblAvail
AvailID
CollectionID
some_other_columns

and

lnkAvailSize
AvailID
SizeID

I want to delete all the availIDs with a CollectionID of 'O'
So....

Delete FROM lnkAvailSize
WHERE AvailID IN (Select AvailID FROM tblAvail WHERE CollectionID = 'O')

except there are no sub-queries in mySQL 3.x.x
So....

Delete FROM lnkAvailSize
    LEFT JOIN tblAvail ON lnkAvailSize.AvailID = tblAvail.AvailID
WHERE tblAvail.CollectionID = 'O'

except JOINs aren't supported in DELETE on any database (as far as I know)
So, now I'm stuck....

Anyone got any thoughts how I can solve this using mySQLFront or SQLyog??
I'm trying to avoid involving writing code... :o/

Regards

Stephen



-- 
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]

-- 
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]

Reply via email to