Create a temp table Insert all the data from the join into that temp table
Do a delete from both (or am I missing something?) Paul > -----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]
