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.