Oladapo, Following Rafal's suggestion, I've tried this and it worked like a charm:
DELETE Customers, orderdetails, orders FROM Customers, orderdetails, orders WHERE Customers.customerid =20 AND orders.CustomerID =20 and orderdetails.orderid=orders.OrderId; Thank you ----- Original Message ----- From: "Oladapo Carew" <[EMAIL PROTECTED]> To: "Anibal Cascais Santos" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, June 20, 2002 4:45 PM Subject: RE: multi-table delete > > I think you have to specify the join from the customers table to the other > tables .. > > -----Original Message----- > From: Anibal Cascais Santos [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 20, 2002 3:38 PM > To: [EMAIL PROTECTED] > Subject: multi-table delete > > > Hi, > > I'm rather new to mySQL, so maybe that's the problem, but here it goes: > I'm using version 4.0.1 alpha-max-nt running on W2k server > the tables I'm using > > CREATE TABLE `customers` ( > `CustomerID` int(11) NOT NULL auto_increment, > `FullName` varchar(50) default NULL, > `EmailAddress` varchar(50) default NULL, > `Password` varchar(50) default NULL, > PRIMARY KEY (`CustomerID`) > ) TYPE=MyISAM; > > CREATE TABLE `orderdetails` ( > `OrderID` int(11) NOT NULL default '0', > `ProductID` int(11) NOT NULL default '0', > `Quantity` int(11) default '0', > `UnitCost` decimal(19,4) default '0.0000', > PRIMARY KEY (`OrderID`,`ProductID`) > ) TYPE=MyISAM; > > CREATE TABLE `orders` ( > `OrderID` int(11) NOT NULL auto_increment, > `CustomerID` int(11) default '0', > `OrderDate` datetime default '0000-00-00 00:00:00', > `ShipDate` datetime default '0000-00-00 00:00:00', > PRIMARY KEY (`OrderID`) > ) TYPE=MyISAM; > > I'm trying to delete Customer 19 and all related orders (in table orders and > orderdetails) in one statement (don't even know for sure if it's possible). > After reading the documentation I've come up with this: > > DELETE Customers, orderdetails, orders FROM > Customers, orderdetails, orders > WHERE Customers.customerid & orders.CustomerID =19; > > this deletes customer 19 from table Customer and all ocurrences of customer > 19 in table Orders. > The problem is it also deletes ALL entries from table OrderDetails. > I want to delete ONLY entries in table OrderDetails that don't have a > corresponding ocurrence in table Orders, i.e., I need to check wich orderID > is present in Orderdetails table that don't exist in the Orders table and > delete that (too confusing?). > So, I've tried this > > DELETE Customers, orderdetails, orders FROM > Customers, orderdetails, orders > WHERE (Customers.customerid & orders.CustomerID =19) > AND orders.OrderID <> orderdetails.OrderID; > > The result is the same: deletes all rows from OrderDetails. > Don't really know what else to do... > > Really apreciate any help you can provide > > Thank you > Aníbal > -------------------------------------------------------------- > Anibal Cascais Santos > www.engrenagem.net > -------------------------------------------------------------- > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
