Update between two tables

[Azathoth]

I have a temporary table #result that looks basically like this:

unit_number deferral_days
1 50
2 20
3 102

And a table lab_unit that has the same fields. I want to update lab_unit for each unit_number with the deferral_days value from #result. Right now I'm using a sub-select that looks like this:

	update
		lab_unit
	set
		deferral_days = 
		(select top 1
			#result.deferral_days
		from
			#result
		where
			lab_unit.unit_number = #result.unit_number)

but it's not working properly as it sets deferral_day values in lab_unit to NULL for certain units, which shouldn't happen. I've heard of an "update from" syntax that might be helpful here, but I can't find good documentation on it...anyone able to help me out?

 

[swampBoogie]

If the deferral_days is set to null it is because there is no corresponding unit_number in the #result table. You can restrict the update to only those that have a unit_number in the #result table

update lab_unit
    set deferral_days =
        (select #result.deferral_days
        from  #result
        where lab_unit.unit_number = #result.unit_number)
where lab_unit.unit_number in (select unit_number from #result)

If you wish to use the non-standard from syntax it is

update lab_unit
   set deferral_days = #result.deferral_days
  from #result inner join lab_unit 
    on lab_unit.unit_number = #result.unit_number

 

[ashishsmith]

you want to transfer data from one table to another, why use queries, use DTS Package, which would copy your data from one table to another one and if you want, it can also work with conditions. I am using it for my transfers from text file to sql table and also for one temp sql table to another sql table. Use that and it is very easy to use. If you wish to use and have question, post here and i will check it to help you.

Ashish Smith