Rthp Rthp - 2 months ago 7
SQL Question

loop through cursor data and insert record if it does not exist

I have following tables:

Table1:

doc_id | item_id
------ | ------
71 | 4
------ | ------
75 | 8
------ | ------
75 | 3
------ | ------
75 | 2
------ | ------
73 | 7


Table2:

id | parent_id | parameter_id
------ | -----------|-------------
1 | 4 | 4
------ | -----------|-------------
2 | 8 | 3
------ | -----------|-------------
3 | 3 | 4
------ | -----------|------------
4 | 2 | 6
------ | -----------|------------
5 | 7 | 4


I want to insert row on Table 2 by following these steps:


  1. Select item_ids from Table1 where document_id = 75;

  2. For each item_id from 1. check if a record exist in Table2 where
    parent_id of Table2 = item_id of Table 1 and parameter_id =4;

  3. If the record does not exist then enter data.

    While inserting it should insert into Table 2 values
    (max(id)+1, parent_id of that particular record(associated Table1's item_id), 4 as parameter_id).



How do I do this? Can someone give me some hint. I am thinking to use cursor and loop through it and check if record exist. Then insert data accordingly??

DECLARE CURSOR C1
IS
SELECT * FROM table 1
WHERE DOC_ID = 75;
BEGIN
FOR EACH_RECORD IN C1 LOOP
// check for Table 2 for EACH_RECORD.CONFIG_ITEM_ID;
END LOOP;
END;
/

Answer
insert into table2
(
    id,
    parent_id,
    parameter_id

)(
    select 
        (select (max(id)) from table2) + rownum,
        item_id,
        4
    from 
        table1
    where
        table1.doc_id = 75 and
        not exists(select 1 from table2 where table1.item_id = table2.parent_id and table2.parameter_id = 4)
)

The Insert Query above is inserting into table2 the max(id) +1, item_id from table1 and 4 as the parameter_id where the doc_id is 75 and there doesn't exist the record in table2 where table1.item_id = table2.parent_id

Comments