Page 1 of 1

Delete from more then one table

Posted: Wed Mar 07, 2007 9:58 pm
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 . "'");

Posted: Wed Mar 07, 2007 10:26 pm
by anjanesh
If the tables were of InnoDB type, you could've done ON DELETE CASCADE ?

Posted: Wed Mar 07, 2007 11:51 pm
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 . "'");

Posted: Thu Mar 08, 2007 12:04 am
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

Posted: Thu Mar 08, 2007 2:38 am
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

Posted: Thu Mar 08, 2007 7:51 am
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.