help with update syntax 2

[Bajwa11]

I have two temp tables in a stored procedure.Both have a date field.

suppose #temp01 has 1000 company records and #temp03 has 100 company records. The 100 companies in #temp03 are already included in #temp01. I need an updat estatement where I need the date field from #temp03 to over write the date field in #temp01 for Just those 100 companies.

UPDATE #Temp01
SET vchCompanyDate = (select #temp03.dtInsertDate from #temp03 where #temp03.CompanyId=#temp01.iCompanyId)

I tried this and it doesnt work. It updates the 100 companies but also replaces the other 900 companies in temp01 with NULL.

(if or a case statement i guess..)




[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[gmmastros]

Try this (or something like it)

Update #Temp01
Set vchCompanyDate = #Temp03.dtInsertdate
From #Temp02 Inner Join #Temp03 On #Temp01.CompanyId = #Temp03.CompanyId

This assumes that all records in #Temp03 have a valid date in dtInsertDate.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JamesLean]

UPDATE t1
SET date = t3.date
FROM #temp01 t1
  JOIN #temp03 t3 ON t1.companyid = t3.companyid

--James

 

[Bajwa11]

thanx guys.. works !

i used the code from james as George used temp02 which i am alreayd using for sometign else. Not to say that it wouldnt have worked but i felt james solution was easier..

thanx again

[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[gmmastros]

Bajwa11,

The #Temp02 thing was a typo. I meant #Temp01. Sorry. I guess that's what I meant by (or something like it).

Thanks for the star, though.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Bajwa11,

Just a piece of advice....don't use the NEWS icon for a question thread. Use the proper icons.

Thanks,

-SQLBill

Posting advice: FAQ481-4875