Deleting Data depending on second Table
The task was to delete data in a table dependend on a join to another table. At first I got the Error: ORA-01752 (cannot delete from view without exactly one key-preserved table). My solution was to do the deletion with an korrelated select:
drop table DELETE_TEST;
CREATE TABLE DELETE_TEST
(
id number,
REPORTINGPERIOD NUMBER,
BRANCHCODE VARCHAR2(10 BYTE)
);
drop table DELETE_CONFIG;
CREATE TABLE DELETE_CONFIG
(
REPORTINGPERIOD NUMBER,
BRANCHCODE VARCHAR2(10 BYTE)
);
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(1,201201,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(2,201202,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(3,201203,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(4,201204,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(5,201205,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(6,201206,'BC1');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(7,201207,'BC1');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(21,201201,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(22,201202,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(23,201203,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(24,201204,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(25,201205,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(26,201206,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(27,201207,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(31,201201,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(32,201202,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(33,201203,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(34,201204,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(35,201205,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(36,201206,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(37,201207,'BC3');
COMMIT;
-- BranchCode 1 - Data has to be deleted before 201206
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201206,'BC1');
-- BranchCode 2 - Data has to be deleted before 201205
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201205,'BC2');
-- BranchCode 3 - Data has to be deleted before 201204
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201204,'BC3');
commit;
DELETE
FROM (SELECT *
FROM DELETE_TEST T
WHERE t.REPORTINGPERIOD < (select c.REPORTINGPERIOD
from DELETE_CONFIG C
where C.BRANCHCODE = t.branchcode)
);
COMMIT;