Update data in one table from data in another table

[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!!!

 

[vongrunt]

If operator_id is unique in temp table, better do it with join:

update A
set A.operator_state = B.cost_center_number
from itof_operator A
inner join #itoftemp B on A.operator_id = B.operator_id

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLBill]

First, a lesson on temporary tables.

#tablename is a local temporary table
##tablename is a global temporary table

#tablename is only available within the 'scope' that it was created and only for the user that created it.

Refer to the BOL for more detailed information.

I think your UPDATE query is out of the scope of the command that created the table. Change the code that creates the table to make the name ##itoftemp and then change it in the update script.

-SQLBill

Posting advice: FAQ481-4875

 

[naxy]

the solution by vongrunt seems to work (testing the data to make sure now).

SQLBill - I understand where you're going with the temp tables. I will read in to it for sure! This is actually handled by a DTS package. The temp table is created, used, and dropped within the same step.