asked on

Append Query for SQL

I have 2 tables.... table A and table B. Table A has about 20 fields and Table B has 2 fields, 'Business_Phone, and P_ID. Table A has Business Phone too. Can i write an append query for checking both tables for 'Business_Phone' and If 'Business_Phone in Table A matches 'Business Phone' in Table B then the P_ID from Table B is coppied to the corresponding Business_Phone row in Table A.

Table A Table B
ID P_ID
Phone_A Business_Phone
Business_Phone
P_ID (currently empty)

So if there is a way to compare the 2 Business phones and if they match take the P_ID from Table B and put it in P_ID of table A.

Thanks for the help.

peter57r

Update tablea inner join tableb
on tablea.business_phone = tableb.business_phone
set tablea.p_id = tableb.p_id

SJT2003A

peter57r:
are you sure that your solution works? ORACLE does not allow you to specify an INNER JOIN in an UPDATE statement. This works for a DAL not for DML. Please check it out. (I have verified it already in ORACLE 9i and 10g, it does'nt work)

rrisal:

Try this. Update the table a with a co-related subquery for the SET variable.

update tableA a set a.p_id = (select b.p_id from tableB b
where b.business_phone = a.business_phone)

When I tried the same, it shows the output as desired.
SQL> ed
Wrote file afiedt.buf

1 create table tableA (
2 id number, phone_a varchar2(12),
3* business_phone varchar2(12),p_id number)
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

1* create table tableB (p_id number, business_phone varchar2(12))
SQL> /

Table created.

SQL>
SQL> insert into tableA values (123,'732-213-3456','908-543-2345',null)
2 /

1 row created.

SQL> insert into tableA values (124,'732-123-4567','908-254-2345',null)
2 /

1 row created.

SQL> insert into tableA values (125,'732-134-5678','908-435-3456',null)
2 /

1 row created.

SQL> insert into tableB values (1234,'908-543-2345')
2 /

1 row created.

SQL> insert into tableB values (1245,'908-254-2345')
2 /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tablea;

ID PHONE_A BUSINESS_PHO P_ID
---------- ------------ ------------ ----------
125 732-134-5678 908-435-3456
123 732-213-3456 908-543-2345
124 732-123-4567 908-254-2345

SQL> select * from tableb;

P_ID BUSINESS_PHO
---------- ------------
1234 908-543-2345
1245 908-254-2345

SQL> ed
Wrote file afiedt.buf

1 update tableA a set a.p_id = (select b.p_id from tableB b
2* where b.business_phone = a.business_phone)
SQL> /

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tablea order by id;

ID PHONE_A BUSINESS_PHO P_ID
---------- ------------ ------------ ----------
123 732-213-3456 908-543-2345 1234
124 732-123-4567 908-254-2345 1245
125 732-134-5678 908-435-3456

SQL>
SQL> select * from tableb;

P_ID BUSINESS_PHO
---------- ------------
1234 908-543-2345
1245 908-254-2345

SQL>

Hope this helps. Good luck :)

peter57r

I'm seeing this Q in the Databases forum - there's no clue as to which dbms the poster is using.

rrisal

ASKER

Sorry Peter, i didn't specify the dbms. I am working with oracle 10g.

SJT2003A: I get "ORA-01427: single-row subquery returns more than one row" error when i run this through toad.

update STG_CONTACT set STG_CONTACT.POLAR_ID = (select STG_AGENCY_POLARID.POLAR_ID from STG_AGENCY_POLARID
where STG_AGENCY_POLARID.BUSINESS_PHONE = STG_CONTACT.BUSINESS_PHONE)

ASKER CERTIFIED SOLUTION

SJT2003A

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rrisal

ASKER

sjt2003A: this seems to work but i'll have to verify the results, could you however please explain to me what the rownum<=1 does? are we saying that there are duplicate business phones? thanks for your help. i'll accept is as a solution as soon as my testing is done!

SJT2003A

The rownum is a pseudo column in oracle and rownum <= 1 condition restricts the output to one row.
In your case, if the subquery finds more than one matching row (ie, duplicate rows), then the ROWNUM <=1 in the subquery restricts the output give only one (ie, first one in the result set) to the UPDATE.

Hope you understand it better now.

Good luck :)