从父表和子表中删除行(Deleting rows from parent and child tables)
假设Oracle 10G中有两个表
TableA (Parent) --> TableB (Child)
TableA中的每一行在TableB中都有几个与它相关的子行。
我想删除TableA中的特定行,这意味着我必须先删除tableB中的相关行。
这将删除子条目
delete from tableB where last_update_Dtm = sysdate-30;
要删除刚刚在子表中删除的行的父行,我可以做这样的事情
Delete from TableA where not exists (select 1 from tableB where tableA.key=tableB.key);
以上将会删除子表中where(last_update_Dtm = sysdate-30)为false的行。 TableA没有last_update_dtm列,因此无法知道在没有子表中的条目的情况下删除哪些行。
我可以在删除之前将密钥保存在子表中,但这似乎是一种昂贵的方法。 删除两个表中的行的正确方法是什么?
编辑
为了更好地解释我想实现的目标,下面的查询将完成我想要做的事情,如果两个表之间没有约束。
Delete from tableA Where exists ( Select 1 from tableB where tableA.key=tableB.key and tableB.last_update_dtm=sysdate-30) Delete from tableB where last_update_dtm=systdate-30
Assume two tables in Oracle 10G
TableA (Parent) --> TableB (Child)
Every row in TableA has several child rows related to it in TableB.
I want to delete specific rows in TableA which means i have to delete the related rows in tableB first.
This deletes the child entries
delete from tableB where last_update_Dtm = sysdate-30;
To delete the parent rows for the rows just deleted in the child table I could do something like this
Delete from TableA where not exists (select 1 from tableB where tableA.key=tableB.key);
The above will will also delete rows in the child table where (last_update_Dtm = sysdate-30) is false. TableA does not have a last_update_dtm column so there is no way of knowing which rows to delete without the entries in the child table.
I could save the keys in the child table prior to deleting but this seems like an expensive approach. What is the correct way of deleting the rows in both tables?
Edit
To explain better what i am trying to achieve, the following query would have done what i am trying to do if there was no constraint between the two table.
Delete from tableA Where exists ( Select 1 from tableB where tableA.key=tableB.key and tableB.last_update_dtm=sysdate-30) Delete from tableB where last_update_dtm=systdate-30
原文:https://stackoverflow.com/questions/5196261