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"