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
