DELETE in mysql?

Thanks to those who replied...but I was hoping perhaps of a creative idea in solving the problem of deleting from multiple tables...I can't believe I will have to delete 265 records (x 3 tables) by hand... is there any other way out? I cannot install version 4 since it is on a hosted site. Thanks --- "Adv. Systems Design" <asd_2000@yahoo.com> wrote: > Maybe my example attempt is misguided but the idea > is > to look up the prod_id from temp to know which > records > to delete in the prod, prod_price, prod_cat tables, > all having the prod_id field as a link...just like > you > can do in a SELECT statement: > select p.product_id, p.product_price > from product_price as p, temp as t > where t.product_id = p.product_id > > Let me know if what I am asking is unclear. > > > --- Rick Emery <remery@excel.com> wrote: > > You cannot refer to other tables. Which row in > temp > > would MYSQL know to > > refer to test the WHERE clause? That is, if temp > > has multiple rows, which > > row in temp would MYSQL check against to determine > > prod_id? > > > > > > -----Original Message----- > > From: Adv. Systems Design > > [mailto:asd_2000@yahoo.com] > > Sent: Friday, February 01, 2002 2:43 PM > > To: PHP List > > Subject: [PHP-DB] DELETE in mysql? > > > > > > sorry if this is painfully obvious but I can't > seem > > to > > figure it out...It seems to me (unless I am > reading > > it > > waay wrong) that the mysql DELETE command does NOT > > allow you to refer to columns from other tables! > > > > I have identified certain records to be deleted > from > > 3 > > tables and created a temp table to hold the > > item_id's > > to look up and delete...BUT HOW?! Ideally it would > > be: > > > > > > DELETE FROM prod, prod_price, prod_cat > > WHERE temp.prod_id = prod.prod_id > > AND temp.prod_id = prod_price.prod_id > > AND temp.prod_id = prod_cat.prod_id > > > > TIA > > > > Luis > > > > ===== > > > ---------------------------------------------------- > > Make it idiot-proof and someone will make a better > > idiot. > > > > __________________________________________________ > > Do You Yahoo!? > > Great stuff seeking new owners in Yahoo! Auctions! > > > http://auctions.yahoo.com > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: > > php-db-unsubscribe@lists.php.net > > For additional commands, e-mail: > > php-db-help@lists.php.net > > To contact the list administrators, e-mail: > php-list-admin@lists.php.net > > > ===== > ---------------------------------------------------- > Make it idiot-proof and someone will make a better > idiot. > > __________________________________________________ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: > php-db-unsubscribe@lists.php.net > For additional commands, e-mail: > php-db-help@lists.php.net > To contact the list administrators, e-mail: > php-list-admin@lists.php.net > ===== ---------------------------------------------------- Make it idiot-proof and someone will make a better idiot. __________________________________________________ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com