Insert record from one table to another table

I have a table TABLE1 which has 5 columns (ROLL_NO, NAME, UNITS, CODE, AMOUNT);

CREATE TABLE TABLE1 (ROLL_NO VARCHAR2(3), NAME VARCHAR2(4), UNITS NUMBER, AMOUNT NUMBER, CODE VARCHAR2(3));
------------------------------------------------------------------------------------------
INSERT INTO TABLE1 VALUES ('101', 'JOHN', 1, 6, 'ABC');
INSERT INTO TABLE1 VALUES ('101', 'JOHN', 2, 6, 'ABC');
INSERT INTO TABLE1 VALUES ('102', 'TOMS', 1, 7, 'ABC');
INSERT INTO TABLE1 VALUES ('102', 'TOMS', 6, 7, 'ABC');
INSERT INTO TABLE1 VALUES ('103', 'FINN', 1, 1, 'BCD');

 ROLL_NO     NAME    UNITS    AMOUNT    CODE
-------------------------------------------------------
 101       JOHN     1         6        ABC
 101       JOHN     2         6        ABC
-------------------------------------------
 102       TOMS     1         7        ABC
 102       TOMS     6         7        ABC

 103       FINN     1         1        BCD

There is second table TABLE2 where we need to insert data from TABLE1

CREATE TABLE TABLE2 (ROLL_NO VARCHAR2(3), NAME VARCHAR2(4), RESULT VARCHAR2(3));

There are three conditions to insert data into TABLE2

1st case : If CODE is 'ABC' and SUM(UNITS) of particular ROLL_NO is equal to AMOUNT then don't insert data into TABLE2
2nd case : If CODE is 'ABC' and SUM(UNITS) of particular ROLL_NO is not equal to AMOUNT then insert data with RESULT column value as 'YES'
3rd case : If CODE is not 'ABC' then RESULT column will be 'YES'.

Note: NAME, CODE and AMOUNT will be same for particular ROLL_NO though ROLL_NO has multiple UNITS.

Example :

 ROLL_NO 102 CODE 'ABC' and two lines with SUM(UNITS) as 7 and its equal to AMOUNT i.e. 7 and  (1st case)     

 ROLL_NO 101 has CODE 'ABC' and two lines with SUM(UNITS) as 3 and its not equal to AMOUNT i.e. 6   (2nd case) 

 ROLL_NO 103 has CODE 'BCD' which is not equal to 'ABC'(3rd case)

At the end TABLE2 should have

   ROLL_NO    NAME      RESULT
   -----------------------------
    101       JOHN       YES 
    103       FINN       YES

I have tried this oracle query but it is inserting data related to 102 ROLL_NO which I don't need

 SELECT T1.ROLL_NO, T1.NAME,
        CASE 
           WHEN T1.CODE <> 'ABC' THEN 'YES'
           WHEN T1.CODE = 'ABC' AND T2.TOT_UNITS <> T1.AMOUNT THEN 'YES'
        END RESULT
 FROM (SELECT DISTINCT ROLL_NO, NAME, AMOUNT, CODE 
       FROM TABLE1 ) T1
 JOIN (SELECT ROLL_NO, SUM(UNITS) AS TOT_UNITS
       FROM TABLE1
       GROUP BY ROLL_NO) T2 ON T1.ROLL_NO = T2.ROLL_NO

I am not able to figure out how to not insert ROLL_NO 102 record into TABLE2..Can anyone provide better query than this if possible? Thank you

CodePudding user response：

A "better" option is to scan table1 only once.

SQL> insert into table2 (roll_no, name, result)
  2  with temp as
  3    (select roll_no, name, sum(units) sum_units, amount, code,
  4       case when code = 'ABC' and sum(units) = amount then 'NO'
  5            when code = 'ABC' and sum(units) <> amount then 'YES'
  6            else 'YES'
  7       end as result
  8     from table1
  9     group by roll_no, name, amount, code
 10    )
 11  select roll_no, name, result
 12  from temp
 13  where result = 'YES';

2 rows created.

SQL> select * from table2;

ROL NAME RES
--- ---- ---
101 JOHN YES
103 FINN YES

SQL>