Conversion of Queries Access to SQL -Update Query 1

[Silvertri]

same situation as previous post, this one is an update query, I have done all the obvious changes however getting syntax error no experience with real SQL queries only Access

TIA Silvertri

UPDATE (tblMainDetails INNER JOIN tblParamsMembers ON tblMainDetails.IncidentNameAlpha = tblParamsMembers.CurrentIncident) INNER JOIN tblOptionsStrategiesAction ON tblMainDetails.IncidentName = tblOptionsStrategiesAction.IncidentName SET tblOptionsStrategiesAction.IncidentController = tblParamsMembers.Members
WHERE (((tblParamsMembers.CurrentIncident)='@param5') AND ((tblParamsMembers.NormalAssign)='Incident Controller') AND ((tblOptionsStrategiesAction.APNo)='@param8'));

 

[JamesLean]

Try this:

UPDATE o
SET IncidentController = p.Members
FROM tblMainDetails m
  JOIN tblParamsMembers p ON m.IncidentNameAlpha = p.CurrentIncident
  JOIN tblOptionsStrategiesAction o ON m.IncidentName = o.IncidentName
WHERE p.CurrentIncident = @param5
  AND p.NormalAssign = 'Incident Controller'
  AND o.APNo = @param8

--James

 

[Silvertri]

G'day James, thanks for the assistance, I would never have got the syntax right on this one!!!

 

[hermanlaksko]

Hi James
Excelent 1 star 4 that one!

I still had one problem though, the collate, so I added: COLLATE SQL_Latin1_General_CP1_CI_AS
(after the join section, before the where section)
And "it ate it" ;-)