August 7, 2009 at 12:35 pm
I'm trying to run this code and I get the error below the code.
UPDATE [tblShipments]
LEFT OUTER JOIN [T Bill To 2] ON ([tblShipments].[Shipment Id] = [T Bill To 2].[Shipment Id]) AND ([tblShipments].[Load Id] = [T Bill To 2].[Load Id])
SET [tblShipments].[Bill To] = [T Bill To 2]![Bill To]
WHERE ((([tblShipments].[Bill To]) Is Null));
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'tblShipments'.
August 7, 2009 at 12:41 pm
Try it this way:
UPDATE
[tblShipments]
SET
[tblShipments].[Bill To] = [T Bill To 2].[Bill To]
FROM
[tblShipments]
LEFT OUTER JOIN [T Bill To 2]
ON ([tblShipments].[Shipment Id] = [T Bill To 2].[Shipment Id])
AND ([tblShipments].[Load Id] = [T Bill To 2].[Load Id])
WHERE
((([tblShipments].[Bill To]) Is Null)) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2009 at 12:46 pm
Gsquared you Rock! It works like a charm!
August 7, 2009 at 12:47 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2009 at 12:55 pm
Besides looking at the syntax for "Update ... From", also note that the second error appears to have be a simple typo. There's an exclamation point in line 4 where one may have expected a period.
Not trying to pile on here; just pointing out that sometimes a carefule re-examination of the code will expose an error.
August 7, 2009 at 6:26 pm
jrw39,
Since you're a newbie, I have to ask... Do you know what "Books Online" is? It's the "help" system that comes with SQL Server. It's one of the best tools that you could ever discover in SQL Server. For example, if you are getting a syntax error on an UPDATE clause, the first thing you might want to try is to double click on the word UPDATE to highlight it and then press the f1 key to activate help on that word.
There is a bar menu on the GUI to get into help for INDEX and SEARCH and a couple of other things. You can also just click on HELP in the main menu bar and select the method of help you want to use. Forget the T-SQL tutorial link in the help menu... it sucks.
The first thing you should lookup in the INDEX when you get into HELP should be "Books Online [SQL Server]" without the quotes.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 7:43 am
On the question you posted on the second copy of this thread:
This:
LEFT JOIN [T Bill To.Conversion]
Needs to be:
LEFT JOIN [T Bill To].[Conversion]
Simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply