How up update a table based on the results of a query from other tables?

How up update a table based on the results of a query from other tables?  I can find the records I want to update based on the following:
SELECT *
FROM         My_Import_Data
WHERE    My_Import_Data.ExternalClaimNumber Not in (select EOB from #tmp_ Imported_Claims)

I want to update my action based on the results of the above query.
Update My_Table
Set Action=’1’
kgittingerAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
using aliases for clarity:

Update My_Table
Set Action=’1’    
FROM  My_Import_Data I
JOIN My_Table T
   on T.Claim = I.ExternalClaimNumber
WHERE I.ExternalClaimNumber not in (select EOB from #tmp_ Imported_Claims)
   
0
 
knightEknightCommented:
How are the two tables related?  My_Import_Data  and  My_Table ?
0
 
kgittingerAuthor Commented:
They all contain a unique number:
My_Import has externalClaim
#tmp_ Imported_Claims has EOB
my_Table has Claim

Thank you.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
knightEknightCommented:
does Claim on My_Table  correlate  directly to externalClaim on My_Import ?
0
 
Ephraim WangoyaCommented:

Update My_Import_Data
Set Action=’1’        
WHERE   My_Import_Data.ExternalClaimNumber Not in (select EOB from #tmp_ Imported_Claims)
0
 
kgittingerAuthor Commented:
Claim on My_Table = ExternalClaim on My-Import

What ewangoya: posted does work, but I am trying to update a 3rd table my_Table based on the results of my query above.
0
 
knightEknightCommented:
Update My_Table
Set Action=’1’    
FROM  My_Import_Data
JOIN My_Table
   on My_Table.Claim = My_Import_Data.externalClaim
WHERE    My_Import_Data.ExternalClaimNumber Not in (select EOB from #tmp_ Imported_Claims)
   
0
 
knightEknightCommented:
just noticed that there is an inconsistency in the column names 'externalClaim' and 'ExternalClaimNumber'  ... use which ever one is correct in the join and in the where clause
0
 
knightEknightConnect With a Mentor Commented:
another way to do the same thing:


Update My_Table
Set Action=’1’
where Claim in (
   SELECT ExternalClaimNumber
   FROM   My_Import_Data
   WHERE ExternalClaimNumber not in (select EOB from #tmp_Imported_Claims)
)

0
 
kgittingerAuthor Commented:
Thanks  no power here  I will try it in the morning
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.