Insert new rows based on conditions - Oracle SQL

Question

I have a table:

table1

unique_id       col_id      col_nm      col_val     sequ 
1               1           testq 1     100         1   
1               2           testc 1     abc         1   
1               1           testq 1     101         2   
1               2           testc 1     xyz         2
1               5           test 5      10          1       
1               8           test 6      100         1   


2               1           testq 1     100         1   
2               2           testc 1     pqr         1   
2               1           testq 1     101         2   
2               2           testc 1     xxy         2
2               5           test 5      qqw         1       
2               8           test 6      100         1   

I need to insert new rows in the table based on the following condition:

1. Find unique_id and sequ of col_id = 1 and col_nm = 'testq 1' and col_val = 100
2. Find col_val of col_id = 2 and col_nm = 'testc 1' and sequ = {sequ of step 1} and unique_id = {unique_id of step 1}.
3. Insert a new row for the corresponding unique_id, with col_id = 100, col_nm = 'test q100c', col_val = {col_val found in step 2}, sequ = {sequ found in step 2}

The output would be:

unique_id       col_id      col_nm      col_val     sequ 
1               1           testq 1     100         1   
1               2           testc 1     abc         1   
1               1           testq 1     101         2   
1               2           testc 1     xyz         2
1               5           test 5      10          1       
1               8           test 6      100         1   
1               100         test q100c  abc         1

2               1           testq 1     100         2   
2               2           testc 1     pqr         2   
2               1           testq 1     101         2   
2               2           testc 1     xxy         2
2               5           test 5      qqw         1       
2               8           test 6      100         1   
2               100         test q100c  pqr         2

Is there anyway in SQL to achieve this?

Answer 1

We can use WITH clause in an INSERT … SELECT construct. So something like this?

insert into table1
with s1 as (
  select t.unique_id 
         , t.sequ 
  from table1 t
  where t.col_id = 1 
  and t.col_nm = 'testq 1' 
  and t.col_val = 100 )
  , s2 as (
     select s1.*
            , t.col_val
      from s1
           join table1 t 
              on t.sequ = s1.sequ
              and t.unique_id = s1.unique_id
  where t.col_id = 2
  and t.col_nm = 'testc 1' 
)
select s2.unique_id
       ,100 as col_id 
       ,'test q100c' as col_nm
       ,s2.col_val
       ,s2.sequ 
from s2
/

I'm not sure I have entirely understood your rules - I used the col_val from step #2 (which is what your expected output shows) rather than the value from step #1 as your rule 3 states - but I hope this gives you a start. Also, this may not be a very efficient approach. I offer no guarantees regarding performance over a large volume of data.