beginner - update query with inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

beginner - update query with inner join

Author

Topic

jjasper
Starting Member

25 Posts

Posted - 2006-05-09 : 12:47:41

Hi
I have been working on this for the last hour - it seems like a simple query - I have 2 tables with the exact same structure - I want to update one table (master_pharmacy) so that the fields will equal the addition of both tables.--- so if master_pharmacy.RCClaims = 5 and pharmacy.RCClaims = 6 then master_pharmacy.RCClaims = 11 when the query is done....

I first used MSAccess and it seemed to work OK but trying to get it to SQL server made it stumble -

UPDATE master_pharmacy INNER JOIN pharmacy ON master_pharmacy.RCClaims = pharmacy.RCClaims SET master_pharmacy.srvprovid = [master_pharmacy].[RCCLaims]+[pharmacy].[RCClaims];

When I try to run it in SQL Server I get: "Incorrect syntax near the keyword 'INNER'"

Could yo show me what I am doing wrong?

Thank- YOu

Kristen
Test

22859 Posts

Posted - 2006-05-09 : 13:07:47

I think that the easiest syntax to understand is using an Alias


UPDATE U
SET     master_pharmacy.srvprovid = [master_pharmacy]U.[RCCLaims] + [pharmacy].[RCClaims]
FROM    master_pharmacy AS U
        INNER JOIN pharmacy 
            ON master_pharmacyU.RCClaims = pharmacy.RCClaims

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources