SQL 从父表和子表中删除行

Deleting rows from parent and child tables

提问by ziggy

Assume two tables in Oracle 10G

假设 Oracle 10G 中有两个表

TableA (Parent) --> TableB (Child)

Every row in TableA has several child rows related to it in TableB.

TableA 中的每一行在 TableB 中都有几个与其相关的子行。

I want to delete specific rows in TableA which means i have to delete the related rows in tableB first.

我想删除 TableA 中的特定行，这意味着我必须先删除 tableB 中的相关行。

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.

以上还将删除子表中 (last_update_Dtm = sysdate-30) 为假的行。TableA 没有 last_update_dtm 列，因此如果没有子表中的条目，就无法知道要删除哪些行。

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

回答by Rajesh Chamarthi

Two possible approaches.

两种可能的方法。

1. If you have a foreign key, declare it as on-delete-cascade and delete the parent rows older than 30 days. All the child rows will be deleted automatically.

2. Based on your description, it looks like you know the parent rows that you want to delete and need to delete the corresponding child rows. Have you tried SQL like this?

     delete from child_table
         where parent_id in (
              select parent_id from parent_table
                   where updd_tms != (sysdate-30)
   

   -- now delete the parent table records

   delete from parent_table
   where updd_tms != (sysdate-30);
   

1. 如果您有外键，请将其声明为 on-delete-cascade 并删除超过 30 天的父行。所有子行将被自动删除。

2. 根据您的描述，您似乎知道要删除的父行，需要删除相应的子行。你试过这样的SQL吗？

     delete from child_table
         where parent_id in (
              select parent_id from parent_table
                   where updd_tms != (sysdate-30)
   

   -- 现在删除父表记录

   delete from parent_table
   where updd_tms != (sysdate-30);
   

---- Based on your requirement, it looks like you might have to use PL/SQL. I'll see if someone can post a pure SQL solution to this (in which case that would definitely be the way to go).

---- 根据您的要求，您可能必须使用 PL/SQL。我会看看是否有人可以为此发布纯 SQL 解决方案（在这种情况下，这肯定是要走的路）。

declare
    v_sqlcode number;
    PRAGMA EXCEPTION_INIT(foreign_key_violated, -02291);
begin
    for v_rec in (select parent_id, child id from child_table
                         where updd_tms != (sysdate-30) ) loop

    -- delete the children
    delete from child_table where child_id = v_rec.child_id;

    -- delete the parent. If we get foreign key violation, 
    -- stop this step and continue the loop
    begin
       delete from parent_table
          where parent_id = v_rec.parent_id;
    exception
       when foreign_key_violated
         then null;
    end;
 end loop;
end;
/

回答by Mark Baker

If the children have FKs linking them to the parent, then you can use DELETE CASCADE on the parent.

如果孩子有 FK 将他们链接到父级，那么您可以在父级上使用 DELETE CASCADE。

e.g.

例如

CREATE TABLE supplier 
( supplier_id numeric(10) not null, 
 supplier_name varchar2(50) not null, 
 contact_name varchar2(50),  
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 
); 



CREATE TABLE products 
( product_id numeric(10) not null, 
 supplier_id numeric(10) not null, 
 CONSTRAINT fk_supplier 
   FOREIGN KEY (supplier_id) 
  REFERENCES supplier(supplier_id) 
  ON DELETE CASCADE 
); 

Delete the supplier, and it will delate all products for that supplier

删除该供应商，它将删除该供应商的所有产品

回答by Gary Myers

Here's a complete example of how it can be done. However you need flashback query privileges on the child table.

这是如何完成的完整示例。但是，您需要对子表的闪回查询权限。

Here's the setup.

这是设置。

create table parent_tab
  (parent_id number primary key,
  val varchar2(20));

create table child_tab
    (child_id number primary key,
    parent_id number,
    child_val number,
     constraint child_par_fk foreign key (parent_id) references parent_tab);

insert into parent_tab values (1,'Red');
insert into parent_tab values (2,'Green');
insert into parent_tab values (3,'Blue');
insert into parent_tab values (4,'Black');
insert into parent_tab values (5,'White');

insert into child_tab values (10,1,100);
insert into child_tab values (20,3,100);
insert into child_tab values (30,3,100);
insert into child_tab values (40,4,100);
insert into child_tab values (50,5,200);

commit;

select * from parent_tab
where parent_id not in (select parent_id from child_tab);

Now delete a subset of the children (ones with parents 1,3 and 4 - but not 5).

现在删除孩子的一个子集（父母为 1,3 和 4 - 但不是 5）。

delete from child_tab where child_val = 100;

Then get the parent_ids from the current COMMITTED state of the child_tab (ie as they were prior to your deletes) and remove those that your session has NOT deleted. That gives you the subset that have been deleted. You can then delete those out of the parent_tab

然后从 child_tab 的当前 COMMITTED 状态中获取 parent_ids（即它们在您删除之前的状态）并删除您的会话尚未删除的那些。这为您提供了已删除的子集。然后你可以从 parent_tab 中删除那些

delete from parent_tab
where parent_id in
  (select parent_id from child_tab as of scn dbms_flashback.get_system_change_number
  minus
  select parent_id from child_tab);

'Green' is still there (as it didn't have an entry in the child table anyway) and 'Red' is still there (as it still has an entry in the child table)

“绿色”仍然存在（因为它在子表中没有任何条目）并且“红色”仍然存在（因为它仍然在子表中有一个条目）

select * from parent_tab
where parent_id not in (select parent_id from child_tab);

select * from parent_tab;

It is an exotic/unusual operation, so if i was doing it I'd probably be a bit cautious and lock both child and parent tables in exclusive mode at the start of the transaction. Also, if the child table was big it wouldn't be particularly performant so I'd opt for a PL/SQL solution like Rajesh's.

这是一个奇异/不寻常的操作，所以如果我这样做，我可能会有点谨慎，并在事务开始时以独占模式锁定子表和父表。此外，如果子表很大，它的性能不会特别好，所以我会选择像 Rajesh 的 PL/SQL 解决方案。