Update one table with data from another?

[naxy]

Hi all!!!

I have a main table (itof_operator) and a working table (#itoftemp). I want to update a field on the main table (itof_operator.operator_state) with a field on the temp table (#itoftemp.cost_center_number) where a field on both tables match (itof_operator.operator_id = #itoftemp.operator_id).

Here's the query I have right now. It keeps throwing Msg 107 (The column prefix '#itoftemp' does not match with a table name or alias name used in the query.) and I can't seem to get by it.


update itof_operator
set operator_state =
(select a.cost_center_number
from #itoftemp a, itof_operator b
where b.operator_ID = a.operator_id)
where itof_operator.operator_id = #itoftemp.operator_id


Thanks all!!!

 

[PHV]

where operator_id In (SELECT operator_id FROM #itoftemp)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886