Deleting records from particular TABLE

I have 1 table i.e. TABLE_1 which has 2 columns i.e. ROLL_NO, ID

 CREATE TABLE TABLE_1 (ROLL_NO VARCHAR2(3), ID VARCHAR2(3));
 -------------------------------------------------------------
 INSERT INTO TABLE_1 VALUES ('101', 'ABC');

I have another 2 tables i.e. TABLE_2 with 3 columns ROLL_NO, LINE_NO, CODE and TABLE_3 with 2 columns i.e. NAME, ORIG_CODE

CREATE TABLE TABLE_2 (ROLL_NO VARCHAR2(3), LINE_NO NUMBER(3), CODE VARCHAR2(3));
---------------------------------------------------------------------------------
INSERT INTO TABLE_2 VALUES ('101', 1, 'AAA');
INSERT INTO TABLE_2 VALUES ('101', 2, 'BBB');
INSERT INTO TABLE_2 VALUES ('101', 3, 'CCC');


CREATE TABLE TABLE_3 (NAME VARCHAR2(5), ORIG_CODE VARCHAR2(7));
---------------------------------------------------------------------------------
INSERT INTO TABLE_3 VALUES ('JOHN', 'ABC AAA');

My question is I need to delete the records from TABLE_1 using other 2 tables TABLE_2 and TABLE_3 in such a way that if any of the LINE_NO of TABLE_2 contains 'AAA' of particular ROLL_NO, don't delete that ROLL_NO from TABLE_1.

This is the oracle code I have written but it is not working as expected:

    DELETE FROM TABLE_1 T1
    WHERE EXISTS ( SELECT 1 FROM TABLE_2 T2, TABLE_3 T3
                   WHERE T1.ROLL_NO = T2.ROLL_NO
                   AND T3.NAME = 'JOHN'
                   AND T1.ID    = SUBSTR(T3.ORIG_CODE,1,INSTR(T3.ORIG_CODE,' ')-1)
                   AND T2.CODE <> SUBSTR(T3.ORIG_CODE,INSTR(T3.ORIG_CODE,' ') 1),3)
                 );

In above code, we can see that LINE_NO 2 and 3 don't have CODE as 'AAA' so that particular ROLL_NO is getting deleted from TABLE_1 which I don't want.

If any LINE_NO has CODE as 'AAA' don't delete from the TABLE_1

CodePudding user response：

Try this:

DELETE FROM TABLE_1 t1
WHERE NOT EXISTS
        (SELECT * FROM TABLE_2 
         INNER JOIN TABLE_3 t3 ON(InStr(t3.ORIG_CODE, CODE) > 0)
          WHERE ROLL_NO = t1.ROLL_NO And 
                t3.NAME = 'JOHN' And
                t1.ID = SubStr(t3.ORIG_CODE, 1, InStr(t3.ORIG_CODE, ' ') - 1))

Regards...