Insert into table values from loop/Select statement as a variable

0
votes

I have difficulty with some task which need loop. As Im pl/sql newbie I don;t know exactly how to aproach that issue, Could anyone take a look?

Maybe the easiest way is to present than describe what I want to do:

Select id from table1 t1 join table2 t2 on t1.column=t2.column2

insert into table2 id from previous operation.

Select id from table1 t1 join table2 t2 on t1.column=ts.column2 where t1.id in (Select id from table1 t1 join table2 t2 on t1.column=t2.column2);

insert into table2 id from previous operation

And now if previous operation return some data then do:

Select id from table1 t1 join table2 t2 on t1.column=t2.column2 WHERE t1.column in (Select id from table1 t1 join table2 t2 on t1.column=ts.column2 where t1.id in (Select id from table1 t1 join table2 t2 on t1.column=t2.column2)
insert into tablr2 id from previous operation

and so on, until select will not return nothing.

Of course I realise that "select" will insert duplicate ID into table3. I know only that i have to use loop but don't know exactly which one and how. Is is possible to handle with that?

edit:

All right once again, I have 2 tables:

Table1 
Column1 
1
2
3
4
5
6
7
8
9

Table2
Column1 Column2 Column3
2345    1   0
5346    2   0
67542   3   23432
3452    4   324665
64356   5   34234
23432   6   0
324665  7   67867
34234   8   0
67867   9   9

Table to insert:

ID 
    1
    2
    3
    4

And as input I got Table3 with ID which I want to INSERT into eg. Table4 (ID).

But some of that ID probably can have some parent/child ID. And that's the main difficult how to do that in the loop. Generally we can find parent/child ID by using statement:

select ID from table1 t1 join Table2 t2 on t1.id=t2.Column2 join Table2 t2 on t2.Column3=t2.Column1

And Now base on "Table to insert" insert statement (into Table4) should insert values: 1,2,3,4 + 6 and 7 (as we can see 6 and 7 has parent id (Column0 ! = 0).

And now we want to check if any of account 6,7 has parent/child ID same as before. So insert into Table4 should insert values: 9 End.

Of course that's only situation describe 2-3 iteration, what If there will be more parent/child ID?

I thought to use some table type, and somehow after every loop put ID to check into table variable and use it to other iteration...

2
sqloracleplsql
I don't understand why you're using the nested subqueries? Perhaps you could edit your question to add in create table statements, along with insert statements to create some sample data, along with the expected output that you're after. That way, we'd have a better idea of what you're trying to do. My gut reaction is that you ought to be able to do this in a single insert into table2 select ... statement - no need for row-by-row aka slow-by-slow processing! – Boneist
Are you looking for a hierarchical query? Try searching for CONNECT BY - is that what you're looking for? – Jon Heller
I edited my post, hope now is describe better.. – bazyl

2 Answers

0
votes

Make it a insert into .. select from like

insert into table2(id)
Select id from table1 t1 join table2 t2 on t1.column=t2.column2
0
votes

From your question it seems that you want to insert all ids into table2 which are common for both table1 and table2 based on column "column" . And leave out the rest of the rows where there is nothing common.

That means u want table2 to contain table2 intersect table1 (for column "column")

Now in sql u can do that easily because it involves set operation rather than operating on a single row .

suppose ur table1 is

id column

1 "A"

2 "B"

3 "C"

and table2 is

id column

3 "A"

4 "C"

Then in table2 u want to insert

1 -- because "A" and has id 1 in table1 and

3 -- because "C" has id 3 in table1

u can simply do that by writing 

insert into table2 (id)
select id from table1 where column in (select column from table2) ;

----I am editing the answer as per your inputs -------

So I am creating the whole tables this time and i have tried replicating every scenrio u mentioned .

Let us first create the req set of tables

            create table table2 (row_id number , id number , parent_id number)

            CREATE table TABLE1 (id number) ;

            CREATE table TABLE3 (id number) ;

            CREATE table TABLE4 (id number) ;

Now let us insert same values(as u mentioned in example) in those tables

            --TABLE1 ---

            INSERT INTO table1 VALUES
            (1);
            INSERT INTO table1 VALUES
            (2);
            INSERT INTO table1 VALUES
            (3);
            INSERT INTO table1 VALUES
            (4);
            INSERT INTO table1 VALUES
            (5);
            INSERT INTO table1 VALUES
            (6);
            INSERT INTO table1 VALUES
            (7);
            INSERT INTO table1 VALUES
            (8);
            INSERT INTO table1 VALUES
            (9);

            --TABLE2 ---
            INSERT INTO table2 VALUES
            (2345   , 1 ,   0) ;
            INSERT INTO table2 VALUES
            (5346   , 2  , 0) ;
            INSERT INTO table2 VALUES
            (67542 ,  3  ,  23432) ;
            INSERT INTO table2 VALUES
            (3452  ,  4  ,  324665);
            INSERT INTO table2 VALUES
            (64356  , 5  ,  34234);
            INSERT INTO table2 VALUES
            (23432 ,  6 ,   0);
            INSERT INTO table2 VALUES
            (324665 , 7 ,  67867);
            INSERT INTO table2 VALUES
            (34234 ,  8 ,  0);
            INSERT INTO table2 VALUES
            (
            67867 ,  9 , 9);

            --TABLE3 ---
            INSERT INTO table3 VALUES
            (1);
            INSERT INTO table3 VALUES
            (2);
            INSERT INTO table3 VALUES
            (3);
            INSERT INTO table3 VALUES
            (4);
            set serveroutput on ;

Running this anoy block will now do the need ful . 

            DECLARE 
            table_var VARCHAR2(30) := 'TABLE3' ; -- replace this with any table
            lvc_cur sys_refcursor ;
            l_num NUMBER ;
            cnt number := 0 ;
            BEGIN

            OPEN lvc_cur FOR 'select distinct id from '||table_var ||' where id in (select table1.id from table1 , table2 where table1.id = table2.id )';

            loop 

            fetch lvc_cur into l_num ;


            exit WHEN lvc_cur%notfound ;

            INSERT INTO table4(ID)
            select id  from table2 connect by prior parent_id = row_id  start with id = l_num   ;

            end loop ;


            END ;

Check this by running the below query 

            select * from table4

                 ID
            ----------
                     1 
                     2 
                     4 
                     7 
                     9 
                     3 
                     6 

             7 rows selected