oracle 同时从多个表中删除记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7076570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete records from multiple tables simultaneously?
提问by sam
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?
我有4张桌子。在第一个表中有 appid 作为主键，在其他三个表中是外键。我想在一个查询中从所有三个中删除数据。我尽力了，但失败了。有人可以帮忙吗？
回答by Tony Andrews
You cannot write a delete statement that references more than one table, you need to write 4 delete statements.
不能写一个引用多个表的delete语句，你需要写4个delete语句。
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.
但是，如果合适，您可以将 3 个子表上的外键定义为“ON DELETE CASCADE”。然后，当您从父表中删除时，3 个子表中的所有关联行也将被删除。这有时很有用，但我不建议将其作为一般做法，因为这对开发人员来说可能很危险且令人困惑。
回答by Stephen ODonnell
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.
没有办法用一条语句从多个表中删除，但更好的问题是为什么需要同时从所有表中删除？在我看来，您还没有完全了解 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.
假设您登录并从表 1 中删除一行，但不提交。就所有其他会话而言，该行尚未删除。如果您打开另一个连接并查询该行，它仍然会在那里。
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.
然后依次从表 2、3 和 4 中删除。您还没有提交事务，因此数据库上的所有其他会话仍然可以看到已删除的行。
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.
同时，其他会话将不再看到您从 4 个表中删除的行，即使您在 4 个单独的语句中进行了删除。
回答by Andrej Ludinovskov
If database is Mysql you can use join in DELETE statement. See http://dev.mysql.com/doc/refman/5.0/en/delete.htmlfor more info.
如果数据库是 Mysql，则可以在 DELETE 语句中使用 join。有关更多信息，请参阅http://dev.mysql.com/doc/refman/5.0/en/delete.html。
