상위 및 하위 테이블에서 행 삭제

상위 및 하위 테이블에서 행 삭제

Oracle 10G에서 두 개의 테이블을 가정합니다.

TableA (Parent) --> TableB (Child)

표 A의 각 행에는 표 B의 각 행과 관련된 몇 개의 자식 행이 있습니다.

저는 표 A의 특정 행을 삭제하고 싶습니다. 즉, 먼저 표 B의 관련 행을 삭제해야 합니다.

그러면 하위 항목이 삭제됩니다.

delete from tableB where last_update_Dtm = sysdate-30;

하위 테이블에서 방금 삭제된 행의 상위 행을 삭제하려면 다음과 같은 작업을 수행할 수 있습니다.

Delete from TableA where not exists (select 1 from tableB where tableA.key=tableB.key);

위에서는 (last_update_Dtm = sysdate-30)이 false인 자식 테이블의 행도 삭제합니다.테이블 A에 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

두 가지 접근법이 가능합니다.

1. 외부 키가 있는 경우 온-삭제-캐스케이드로 선언하고 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);
   

---- 요구 사항에 따라 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;
/

자식에게 부모와 연결하는 FK가 있는 경우 부모에서 DELETE CASCADE를 사용할 수 있습니다.

예.

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

공급업체를 삭제하면 해당 공급업체의 모든 제품이 삭제됩니다.

다음은 어떻게 할 수 있는지에 대한 완벽한 예입니다.그러나 하위 테이블에 대한 플래시백 쿼리 권한이 필요합니다.

설정은 여기 있습니다.

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

이제 자녀의 하위 집합(부모가 1, 3, 4이지만 5는 아님)을 삭제합니다.

delete from child_tab where child_val = 100;

그런 다음 child_tab의 현재 COMITED 상태(즉, 삭제 전 상태)에서 parent_ids를 가져와 세션에서 삭제하지 않은 것을 제거합니다.그러면 삭제된 부분 집합이 나타납니다.그런 다음 parent_탭에서 삭제할 수 있습니다.

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

'녹색'은 계속 유지(어차피 하위 테이블에 항목이 없었기 때문에)되고 '빨간색'은 계속 유지(하위 테이블에 항목이 있기 때문에)됩니다.

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

select * from parent_tab;

이국적이고 특이한 수술이기 때문에, 만약 제가 이 수술을 한다면, 저는 아마 조금 조심해서 거래를 시작할 때 아이와 부모 테이블을 모두 독점 모드로 잠글 것입니다.또한, 자식 테이블이 크다면 성능이 특별히 좋지 않을 것이기 때문에 Rajesh와 같은 PL/SQL 솔루션을 선택했습니다.

언급URL : https://stackoverflow.com/questions/5196261/deleting-rows-from-parent-and-child-tables