Delete from more then one table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

Delete from more then one table

Post by SmokyBarnable »

I was wondering if I could combine these two statements.

Code: Select all

//delete customer from customer table
tep_db_query("delete from customers where customers_id = '" . $ebay_customers_id . "'");
//delete basket contents for customer
tep_db_query("delete from customers_basket where customers_id = '" . $ebay_customers_id . "'");
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

If the tables were of InnoDB type, you could've done ON DELETE CASCADE ?
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

Post by SmokyBarnable »

So I take it I can't do this.

Code: Select all

tep_db_query("delete from customers_basket, customers where customers_id = '" . $ebay_customers_id . "'");
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Thought this would work but deleted ALL rows !

Code: Select all

DELETE t1.*, t2.* FROM 
`t1` t1, `t2` t2
WHERE t1.`id`=2 OR t2.`id`=2
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Yes you can delete from more than one table.

I suggest you read the MySQL manual at the MySQL website.

http://dev.mysql.com/doc/refman/4.1/en/delete.html
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

If database is following Relations, i mean, if 'customer_id' is defined as Foreign Key in 'customers_basket' table.
Then you have to run it in this order:

Code: Select all

 //delete basket contents for customer
tep_db_query("delete from customers_basket where customers_id = '" . $ebay_customers_id . "'");
//delete customer from customer table
tep_db_query("delete from customers where customers_id = '" . $ebay_customers_id . "'");
I hope this will work.
Post Reply