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:
Select item_ids from Table1 where document_id = 75;
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;
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;
/