5

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 three in one query. I tried my best but failed. Can anybody help?

Improve this question
1
  • This is the problem the referential action ON DELETE CASCADE was invented to solve. Unlike @Tony Andrews, I do recommend it as a general practice. Commented Aug 16, 2011 at 12:30

3 Answers 3

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Can you please elaborate on the dangerous and confusing nature of cascading foreign keys? Because I'm a little surprised by this remark.
@Rene, OK well as it happens I currently work on a project where someone has set up lots of foreign keys as ON DELETE CASCADE. So for example if a user does something like DELETE order_status_lookup WHERE status_code = 'SHIPPED' then it succeeds and all orders with status 'SHIPPED' get deleted too. I call that a but dangerous! I do actually use ON DELETE CASCADE, but only where it really makes sense.
So what you are saying is that it is a tool that can be very usefull but used in the wrong way can cause harm. A bit like a chainsaw isn't it? But would you then recommend cutting down all those trees manually?
I wouldn't touch a chainsaw for any money! ;-)
And I said in my answer "this can be useful sometimes", I didn't say "never" did I?
|
3

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.

Improve this answer

1 Comment

I think you're being a little harsh. I can see merit in a DELETE ALL syntax analagous to INSERT ALL: it would be neat to zap all the related records in a set of tables without having to worry about dependencies and without using deferred constraints. (Although I admit my need for this is usually for managing Test Data rather than in production environments).
-2

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.

Improve this answer

1 Comment

In addition to another comment(transaction or using delete cascade) you can use trigger for delete from child tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.