I need to update a table's field from another table:

The sql should look something like this. Hoiwever, I am not getting the syntax correct.

update EARNINGS A
set A.hrs_ytd = A.hrs_ytd - (select B.hrs_ytd from Work B
where A.emplid=B.emplid
and B.balance_period = '9'
and B.balace_id='CY'
and B.balance_year='2001')
where A. balance_qtr='1'
and A.balance_year='2002'
and A.balance_id='FY'
/

THis definitely does not work. How do I get the value of B.hrs_ytd in the set statement.

For example, if A.hrs_ytd = 200 and B.hrs_ytd=50 for the same employee, then the new value
of A.hrs_ytd = A.hrs_ytd - B.hrs_ytd (ie, A.hrs_ytd = 100 - 50).

Can somebody help?

Oracle LazyDBA home page