If you're using mysql 4.0, you might try: DELETE addrbkb FROM addrbkA,addrbkB WHERE addrbkA.phone=addrbkB.phone;
This functionality was added with 4.x. I haven't tested this, so BEWARE The following is from the 4.0 manual: DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id In the above case we delete matching rows just from tables t1 and t2. -----Original Message----- From: Juergen Schreck [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 8:06 AM To: MYSQL List Subject: [SQL] How to delete multiple instances Hi, I'm wondering what the best way is to remove multiple records matching against multiple instances found in another table. I'd like to avoid using a scripting solution doing this in a loop. Let's say I have two address books and there is some cross-over in entries. I want to delete from addressbook 'A' all records that have a phone number found in addressbook 'B'. In looking through the mysql docs, I realize that mysql doesn't [seem to] support the IN() operater and also doesn't allow sub-queries? I've worked in primebase before and there I would have done this: DELETE FROM addrbkA WHERE phone IN (SELECT phone from addrbkB) Is there any SQL solution to this in mySQL? Using temp tables or external files and/or multiple queries would be ok with me? Thanks much in advance. Regards, Juergen --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
