Insert into a table based on a cursor

Question

I have the below tables:

Table1:

    | Resp_ID | Description |
    |       1 |          AA |
    |       2 |          AA |
    |       3 |          AA |

Table 2:

    | ORG_ID  | Resp_ID |        Date | GID |
    |    001  |       1 |   08-SEP-14 | 112 |
    |    002  |       1 |   08-SEP-14 | 112 |
    |    003  |       3 |   08-SEP-14 | 114 |
    |    004  |       5 |   08-SEP-14 |     |
    |    005  |       5 |   08-SEP-14 |     |
    |    006  |       6 |   08-SEP-14 |     |

My requirement is like this: I need to insert GID into Table2 if Resp_ID in Table2 is not found in Table1.

Hence I wrote the below script but it doesn't work:

    DECLARE
      CURSOR resp_id_cursor
      IS
        SELECT resp_id
        FROM   Table1
        WHERE  description LIKE '%AA%';

        flag NUMBER;

    BEGIN
       FOR resp_cur IN resp_id_cursor
         SELECT 1
         INTO   flag
         FROM   Table2 a
         WHERE  a.resp_id = resp_cur.resp_id;

         IF flag != 1 THEN
           INSERT INTO Table2 (GID) 
           VALUES(115);
         END IF;
       END LOOP;
     END;
     /

Please advice.. Thanks!

Answer 1

Please try this code,

declare  

        cursor r1 is
     SELECT Resp_ID 
     FROM Table2;

   gid1 number:= 115;
    flag number:=0;
   begin

   FOR c1 in r1
   LOOP
     begin
   dbms_output.put_line('in here');
   SELECT count(*)
         INTO   flag
         FROM   Table1 a
         WHERE  a.Resp_ID  = c1.Resp_ID ;
         dbms_output.put_line('flag is '||flag);
   dbms_output.put_line( c1.Resp_ID );
         IF (flag = 0) THEN
         dbms_output.put_line('doesnt exist');
     update Table2 set gid=gid1 where Resp_ID =c1.Resp_ID ;     
        dbms_output.put_line('update value for Resp_ID  '||c1.Resp_ID );
         dbms_output.put_line('gid inserted is  '||gid1);
     flag:=0;
     commit;
     dbms_output.put_line('commited');
         END IF;

       exception
       when no_Data_found then
         dbms_output.put_line('no data found for Resp_ID  '||c1.Resp_ID );
         end;
       gid1:=gid1+1;
   END LOOP;

   END;