Page 1 of 1

Deleting data from 2 tables

Posted: Wed Nov 12, 2003 2:29 am
by sergei
Is there any way to delete data from two tables ?

I'm doing a search on two tables, displaying the data and giving the option to delete the displayed data.

Can I delete the data where table1.id = table2.id ?

Must I use two queries ? If so, how do I make sure that all the data is deleted.

Posted: Wed Nov 12, 2003 2:43 am
by Jean-Yves
Depends on the database and table type that you are using. If your database/tables support referential integrity and cascaded deletes, then the easiest way is to create a relationship between the tables with cascaded deletes enabled, so that when for example you delete an order, all order lines that have a foreign key linked to the order id are also deleted, automatically.

See the MySQL manual, section "7.5.4.2 Foreign Key Constraints" for more details (this reference is in v4.0.5 of the manual at least)