Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-31
10
Medium Priority
?
428 Views
Last Modified: 2012-08-13
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’
0
Comment
Question by:kgittinger
  • 6
  • 3
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 37059002
How are the two tables related?  My_Import_Data  and  My_Table ?
0
 

Author Comment

by:kgittinger
ID: 37059077
They all contain a unique number:
My_Import has externalClaim
#tmp_ Imported_Claims has EOB
my_Table has Claim

Thank you.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 37059088
does Claim on My_Table  correlate  directly to externalClaim on My_Import ?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37059124

Update My_Import_Data
Set Action=’1’        
WHERE   My_Import_Data.ExternalClaimNumber Not in (select EOB from #tmp_ Imported_Claims)
0
 

Author Comment

by:kgittinger
ID: 37059341
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 37059361
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 37059376
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
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 37059389
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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 2000 total points
ID: 37059527
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
 

Author Closing Comment

by:kgittinger
ID: 37059721
Thanks  no power here  I will try it in the morning
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question