Deleting data from 2 tables

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
sergei
Forum Commoner
Posts: 33
Joined: Mon Oct 06, 2003 4:17 am

Deleting data from 2 tables

Post 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.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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)
Post Reply