1

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;
/
Rthp
  • 85
  • 1
  • 13
  • Welcome to Stackoverflow. We're here to help you - not do the work for you: you should show us what have you tried and where have you failed (which also means - asking a more focused question). – Nir Alfasi Sep 08 '16 at 17:18
  • Ya. The other question/answer mentions the merge command. that is probably the easiest way to do it. – EvilTeach Sep 08 '16 at 17:20
  • As JNevill commented, this can be a single SQL statement. Start by trying to define a select statement that identifies the rows in Table 1 that should be entered into Table 2. Use a "where not exists" correlated subquery to text whether a row exists in table 2 that matches the conditions you have described. From that query you should be able to develop your insert statement. Try to use a sequence to give you the value for the id column rather than trying to increment the maximum currently in the table. – David Aldridge Sep 08 '16 at 17:35
  • @alfasin Till now I have a cursor that is holding records whose doc_id is 75. Got stuck for a while and now I am trying other steps too. That's the reason I asked for a hint. Thanks! – Rthp Sep 08 '16 at 17:35
  • Thank you everyone. I am just asking some hints not the whole answer. Appreciated! – Rthp Sep 08 '16 at 17:35
  • Since you've added the relevant code to the question - you may disregard my initial comment. – Nir Alfasi Sep 08 '16 at 17:37

1 Answers1

1
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

Stivan
  • 1,128
  • 1
  • 15
  • 24
  • A few problems here. 1) You're using SQL Server syntax (`max_id = ...`). 2) The `(max(id) + 1)` part of the query won't work, because all the "new" rows will share the same new `max_id` value, so you would be inserting duplicates. – sstan Sep 08 '16 at 17:46
  • @sstan I just realized that. Thanks for bringing that up. This issue could be avoided by having id as identity which he would have to make new column or create new table and copy old table over – Stivan Sep 08 '16 at 17:48
  • @sstan I just updated where you said I have some problems. Is there still something you see wrong? – Stivan Sep 08 '16 at 17:55
  • Actually, yes. The `rownum` value will always be one in this case (think about it). And even if you fixed it, it's very expensive to find the max value of the table for every row returned by the query, which is what it's doing. Then there are concurrency concerns, etc.. Using a sequence would probably be best, but I'm sure OP will figure it all out with the duplicate question. – sstan Sep 08 '16 at 18:01
  • @sstan fixed it once again. Yes I suppose you are right :P Thanks for the help – Stivan Sep 08 '16 at 18:04
  • Wow without cursor! Thanks. I didn't think about it. – Rthp Sep 08 '16 at 19:41
  • @Rthp Anytime. Although this is very bad practice to increment table2.id (this should be set to identity column) it will auto increment on its own. To do this you can 1.) add new column set it to auto increment 2.) create new table and copy data from old table over which will also be auto increment. – Stivan Sep 08 '16 at 19:46