Inserting Parent Keys into a table
I am trying to do an outer join on two tables and insert the resulting values into a
third table. In addition, I also need to insert all the parent keys to the row that I
am inserting into this table. I have provided the details below.
create table target (PART_ID VARCHAR2(256), Q_ID NUMBER(38));
create table qtab (Q_ID NUMBER(38), SERIAL_NO VARCHAR2(20));
create table sers (PART_ID VARCHAR2(256), SERIAL_NO VARCHAR2(20));
create table pq_tab (Q_ID NUMBER(38), PARENT_Q_ID NUMBER(38));
ALTER TABLE pq_tab ADD ( PRIMARY KEY (q_id) );
ALTER TABLE qtab ADD ( PRIMARY KEY (q_id,serial_no) );
ALTER TABLE sers ADD ( PRIMARY KEY (part_id, serial_no) ) ;
insert into qtab values (64,'A1-b');
insert into sers values ('1A', 'A1-b');
insert into pq_tab values (64,63);
insert into pq_tab values (63,48);
insert into pq_tab values (48,0);
SQL> select a.part_id, b.q_id from sers a, qtab b where a.serial_no=b.serial_no;
Now the part_id, q_id selected above should be inserted into the target table's part_id
and q_id respectively. In addition to this, I will need to enter all the parent_q_ids
into this table as well for a given part_id. So, for the given data, the target table
should have the following data:
select * from target:
I am not quite sure how to accomplish this. Here is what I need help with.
1) Insert the above 2 values into the target table.
2) Also insert all the parent_q_ids for 64 (63 and 48) into the target table.
I have been trying to use the CONNECT BY statement but was unable to come up with the appropriate command.
I am running Oracle 126.96.36.199.0 on Solaris 9.
Any help would be greatly appreciated.
Please let me know if you need further details.
Last edited by sankar6254; 06-13-2005 at 09:40 PM.
Sankar B. Mandalika
For a given part_id, the below sql gives the required resultset.
Test the SQL statement with many rows in all tables. This is very important. With limited data, the SQL may return correct result set.
var b1 varchar2(30);
exec :b1 := '1A';
select :b1 part_id, q_id
start with parent_q_id = (select parent_q_id
where q_id =
from sers a, qtab b
where a.serial_no = b.serial_no
and a.part_id = :b1)
connect by prior parent_q_id = q_id
You have to ensure that sub queries return a single row. Other wise it will blow out. May be you need to use max or min in the sub queries.
Last edited by tamilselvan; 06-14-2005 at 03:02 PM.
That was perfect. Worked like a charm. Thanks for your quick help.
Sankar B. Mandalika
I never designed a DB in which an entity has self references. Some how I dislike it.
Click Here to Expand Forum to Full Width