Inner Join to Update existing records

[firefly2k8]

Thanks in advance for any help.

I have a table 'Project Table 1' and a query Source_Rates. The following inner join query works nicely, adding two columns to 'Project Table' with the correct details from Source_Rates.

SELECT [Project Table 1].* , [Source_Rates].[Rate1], [Source_Rates].[Rate2]
FROM [Project Table 1] 
INNER JOIN Source_Rates ON ([Project Table 1].[Date1]=Source_Rates.[Date1]) AND ([Project Table 1].[Identifier]=Source_Rates.[Identifier])
WHERE [Project Table 1].[Type]="TypeA" And Not [Project Table 1].[Type] Is Null And [Project Table 1].[Type]<>"";

However rather than add columns, I want to overwrite records where the criteria match. I tried the following but with no luck. Can you suggest where I may be going wrong?

UPDATE [Project Table 1]
SET [Project Table 1].[Rate1] = [Source_Rates].[Rate1], [Project Table 1].[Rate2] = [Source_Rates].[Rate2]
FROM [Project Table 1] 
INNER JOIN Source_Rates ON ([Project Table 1].[Date1]=Source_Rates.[Date1]) AND ([Project Table 1].[Identifier]=Source_Rates.[Identifier])
WHERE [Project Table 1].[Type]="TypeA" And Not [Project Table 1].[Type] Is Null And [Project Table 1].[Type]<>"";

 

[jdraw]

I think the UPDATE query syntax

UPDATE [Project Table 1]
SET [Project Table 1].[Rate1] = [Source_Rates].[Rate1], [Project Table 1].[Rate2] = [Source_Rates].[Rate2]
FROM [Project Table 1] 
INNER JOIN Source_Rates ON ([Project Table 1].[Date1]=Source_Rates.[Date1]) AND ([Project Table 1].[Identifier]=Source_Rates.[Identifier])
WHERE [Project Table 1].[Type]="TypeA" And Not [Project Table 1].[Type] Is Null And [Project Table 1].[Type]<>"";

should be

UPDATE [Project Table 1]
INNER JOIN Source_Rates ON ([Project Table 1].[Date1]=Source_Rates.[Date1]) AND ([Project Table 1].[Identifier]=Source_Rates.[Identifier])
SET [Project Table 1].[Rate1] = [Source_Rates].[Rate1]
, [Project Table 1].[Rate2] = [Source_Rates].[Rate2]
 WHERE
 [Project Table 1].[Type]="TypeA" And Not [Project Table 1].[Type] Is Null And [Project Table 1].[Type]<>"";

****untested****

 

[firefly2k8]

Seems to work.

Thanks a lot!