Delete records from multiple tables simultaneously?

I have 4 tables. In first table has appid as primary key and in other three table its foreign key. I want to delete data from all the thr开发者_开发知识库ee in one query. I tried my best but failed. Can anybody help?

You cannot write a delete statement that references more than one table, you need to write 4 delete statements.

However, if appropriate, you can define the foreign keys on the 3 child tables to "ON DELETE CASCADE". Then when you delete from the parent table, all associated rows from the 3 child tables are also deleted. This can be useful sometimes, but I would not recommend it as a general practice as it can be dangerous and confusing for developers.

There is no way to delete from many tables with a single statement, but the better question is why do you need to delete from all tables at the same time? It sounds to me like you don't fully understand how transactions work in Oracle.

Lets say you login and delete a row from table 1, but do not commit. As far as all other sessions are concerned, that row has not been deleted. If you open another connection and query for the row, it will still be there.

Then you delete from tables 2, 3 and then 4 in turn. You still have not committed the transaction, so all other sessions on the database can still see the deleted rows.

Then you commit.

All at the same time, the other sessions will no longer see the rows you deleted from the 4 tables, even though you did the deletes in 4 separate statements.

If database is Mysql you can use join in DELETE statement. See http://dev.mysql.com/doc/refman/5.0/en/delete.html for more info.