Update table using joins

[TysonLPrice]

I'm having trouble putting together an update. The query to select the data is:

select  c.coordinator,  x.coordinator
from ##Xref x, Claim c (nolock) 
join iwlocation (nolock) on fkiwlocation = pkiwlocation
join injuredworker iw (nolock) on fkinjuredworker = pkinjuredworker
where fkemployeraddress = 47556
and iw.SSN = x.SSN[\code]

I neeed to set the Claim table coordinator (c.coordinator)to the ##XREF table coordinator (x.Coordinator) using the same look up logic.

I can't seem to get.  Can somebody help me out?

 

[markros]

;with cte as (select c.PK, c.coordinator,  x.coordinator as NewCoordinator
from ##Xref x , Claim c (nolock) -- What is the join condition between these two - do you want a cross join ?
join iwlocation (nolock) on fkiwlocation = pkiwlocation
join injuredworker iw (nolock) on fkinjuredworker = pkinjuredworker
where fkemployeraddress = 47556
and iw.SSN = x.SSN)

update cte set Coordinator = NewCoordinator

PluralSight Learning Library

 

[SQLSister]

update c
set coordinator=  x.coordinator
--select  c.coordinator,  x.coordinator
from ##Xref x
join Claim c (nolock) 
     on ?
join iwlocation (nolock) 
     on fkiwlocation = pkiwlocation
join injuredworker iw (nolock) 
     on fkinjuredworker = pkinjuredworker
where fkemployeraddress = 47556
     and iw.SSN = x.SSN

Never use implied joins, this one would give you a cross join which I cannot think you would want for an update. Implied joins are an exterely poor practice and implied joins combined with explicit joins can create some strange results.

"NOTHING is more important in a database than integrity." ESquared

 

[TysonLPrice]

Thanks for your help!