Append Query for SQL

rrisal
rrisal used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Update tablea inner join tableb
on tablea.business_phone = tableb.business_phone
set tablea.p_id = tableb.p_id

Commented:
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 :)
I'm seeing this Q in the Databases forum - there's no clue as to which dbms the poster is using.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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)
Commented:
I think you have duplicate rows in your table. OK, try adding ROWNUM <= 1 in the subquery. It would look like:

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
AND ROWNUM <= 1)

Try it out and let me know.  Otherwise, you please post a few rows those giving trouble with this UPDATE.

Good luck :)

Author

Commented:
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!

Commented:
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 :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial