Syntax error in query migrated from Access

[DataMiner]

What's wrong with my syntax here??

UPDATE PastForecast_Append1 LEFT JOIN PastForecast
ON (PastForecast_Append1.Noun=PastForecast.Noun) AND (PastForecast_Append1.FcstDate=PastForecast.Date)
SET PastForecast.Noun = pastforecast_Append1.noun, PastForecast.[Date] = pastforecast_append1.fcstdate, PastForecast.Fcst = pastforecast_append1.fcstqty, PastForecast.EntryDate = getdate();

This works fine in Access 2002 but when I try it in SQL server I keep getting "Incorrect syntax near the keyword 'LEFT'.

 

[DataMiner]

I changed to

UPDATE PastForecast_Append1 LEFT JOIN PastForecast
ON (PastForecast_Append1.Noun=PastForecast.Noun) AND (PastForecast_Append1.FcstDate=PastForecast.dDate)
SET PastForecast.Noun = pastforecast_Append1.noun, PastForecast.dDate = pastforecast_append1.fcstdate, PastForecast.Fcst = pastforecast_append1.fcstqty, PastForecast.EntryDate = getdate();

but still get the same error

 

[boblarson]

An update query in T-SQL is not formulated the same as in Access. You need to move things around a bit.

Look here and see if you can spot what needs to change:
http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

 

[CameronM]

As a stating point, you query needs to look something like this (as the link from Bob's post shows);

[SIZE=2][COLOR=#0000ff]UPDATE[/COLOR][/SIZE][SIZE=2] PastForecast[/SIZE]
[SIZE=2][COLOR=#0000ff]SET[/COLOR][/SIZE]
[SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Noun [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] pastforecast_Append1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]noun[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2][Date] [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] pastforecast_append1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]fcstdate[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Fcst [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] pastforecast_append1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]fcstqty[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]EntryDate [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][SIZE=2][COLOR=#808080]()[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE]
[SIZE=2]PastForecast_Append1 [/SIZE][SIZE=2][COLOR=#808080]LEFT[/COLOR][/SIZE][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][SIZE=2] PastForecast [/SIZE][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]PastForecast_Append1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Noun[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Noun[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]PastForecast_Append1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]FcstDate[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]PastForecast[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Date[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]

One caveat if you are using datetime fields in SQL server, you may find that the two tables never find a matching record, as datetime is precise to seconds.

 

[DataMiner]

Thanks much, that did the trick.