从父表和子表中删除行 [英] Deleting rows from parent and child tables

问题描述

假设在Oracle 10G中有两个表

Assume two tables in Oracle 10G

TableA (Parent) --> TableB (Child)

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

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.

这将删除子条目

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);

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

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?

为了更好地解释我要实现的目标，如果两个表之间没有约束，则以下查询将完成我想做的事情.

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

推荐答案

两种可能的方法.

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

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.

根据您的描述，您似乎知道要删除的父行，并且需要删除相应的子行.您是否尝试过这样的SQL?

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)

-现在删除父表记录

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

----根据您的要求，看来您可能必须使用PL/SQL.我将看看是否有人可以为此发布一个纯SQL解决方案(在这种情况下，绝对是可行的方法).

---- 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).

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;
/

这篇关于从父表和子表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！