Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to INSERT into table using CORRELATED subquery

zephyr223Oct 14 2008 — edited Oct 15 2008
I have 3 tables:
1.TEMP_PHONE(person_id, phonenumber, phone_type) - this holds all phone numbers relating to a person(just a temporary holding area)

2.PHONE_CONNECT(PERSON_ID, PHONE_ID) this table shows all the phone numbers relating to an individual. Phone_id is a unique number to identify a phonenumber and type(cell, work, home) - so in this table a person can have multiple phone ids)

3.MASTER_PHONE(PHONE_ID, PHONENUMBER, PHONE_TYPE) this is a master phone table. each combination of phone number and type has a unique identifier-phone_id.

What i need to figure out is how to populate PHONE_CONNECT with the information from TEMP_PHONE IF PERSON_ID already exists but phone_id is different. In other words, if the person gets a new phone number, i need to insert a new row into phone_connect.

Before that step is started, the master_phone is populated first with a new phone_id associated to the phonenumber/type
any help would be much appreciated. Thanks in advance.

So far, this is what i have come up with, but not sure if it makes sense:

insert into phone_connect(person_id)
select a.person_id
from temp_phone a
where
person_id = (select b.person_id from phone_connect b, master_phone c
where
a.person_id=b.person_id
and b.phone_id <> c.phone_id
and c.phonenumber||c.phone_type=a.phonenumber||a.phone_type);

update phone_connect c
set phone_id=(
select b.phone_id
from temp_phone a, master_phone b
where a.person_id = c.person_id
and a.phonenumber||a.phone_type = b.phonenumber||b.phone_type)
where phone_id is null;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2008
Added on Oct 14 2008
4 comments
797 views