Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating one table with the data from another table with matching field
Cleve Sharpe wrote:
>
> SKMGMT wrote in article <19970917015900.VAA19564_at_ladder01.news.aol.com>...
>
> >Master_Table: Name Varchar2(20)
> > Age Number(3)
> > Salary Number (8)
> > Ssn Number (10)
> >
> > Payroll_Table Ssn Number (10)
> > Avg_Salary Number (8)
> >
> >Update the salary column of master table with avg_salary/12 for
> >corresponding ssn. Use only SQL query. PL/SQL Block not allowed.
>
> ---------------------------------------
> You may have already received the answer to this but here it is:
>
> update master_table
> set salary = ( select payroll_table.avg_salary/12
> from payroll_table
> where payroll_table.ssn = master_table.ssn)
> ----------------------------------------
> Hope that helps.
>
> Cleve
> Community Blood Centers of South Florida
> e-mail: cleve_at_nospam.cbcsf.org
>
> /* remove nospam from my e-mail address to send me e-mail */
Remember to add an outer WHERE clause to your update (and not
just to the SET subsquery) unless you want to update *all*
rows in the master_table.
The WHERE clause you need is often similar to, or derived from,
the WHERE clause of the subquery:
e.g.
WHERE master_table ssn in
(select ssn from payroll_table)
or
WHERE EXISTS
(select null from payroll_table P
where P.ssn = master_table.ssn)
If you omit the outer WHERE clause, any rows in the master_table for which the SET subquery returns no row will have the value of salary set to null!
HTH
-- Chrysalis FABRICATI DIEM, PVNC ('To Protect and to Serve') Terry Pratchett : "Guards Guards"Received on Fri Sep 26 1997 - 00:00:00 CDT