ERROR -- Incorrect syntax near the keyword 'LEFT'

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

SQL Server Development (2000)

ERROR -- Incorrect syntax near the keyword 'LEFT'

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-28 : 11:25:43

Hi, does anyone have any ideas as to why the following SQL statement produces this error? I'm using SQL Server and the statement is embedded in an ASP front page.....however I also get the same error when trying to run the statement as a Stored Procedure on the server.

SQL Statement:

UPDATE dbo.tContracts LEFT JOIN dbo.tAppliance ON tContracts.ContractNo = tAppliance.ContractNO

SET tContracts.QTR1=(tContracts.QTR1+tAppliance.PQ1), tContracts.QTR2=(tContracts.QTR2+tAppliance.PQ2), tContracts.QTR3=(tContracts.QTR3+tAppliance.PQ3), tContracts.QTR4=(tContracts.QTR4+tAppliance.PQ4), tContracts.FirstQTRWritten = 'Y', tContracts.ContractValue = (tContracts.ContractValue+tAppliance.Cost), tContracts.CurrentValue = (tContracts.ContractValue+tAppliance.Cost), tContracts.QTRZero = 'S', tContracts.LastQTRWritten = 'N', tContracts.QTRPay1 = 'N', tContracts.QTRPay2 = 'N', tContracts.QTRPay3 = 'N', tContracts.QTRPay4 = 'N'

WHERE tContracts.ContractNo=('"& ContractNo &"')

Ther error is:

Incorrect syntax near the keyword 'LEFT'

Thanks!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 11:41:25

Have you ever checked the syntax of UPDATE statement in BOL...It's in the following form:

Update <table_name>
Set <col_name> = <expression>
{From <table_name>}
{Where <condition>}

UPDATE dbo.tContracts 
SET tContracts.QTR1=(tContracts.QTR1+tAppliance.PQ1), tContracts.QTR2=(tContracts.QTR2+tAppliance.PQ2), tContracts.QTR3=(tContracts.QTR3+tAppliance.PQ3), tContracts.QTR4=(tContracts.QTR4+tAppliance.PQ4), tContracts.FirstQTRWritten = 'Y', tContracts.ContractValue = (tContracts.ContractValue+tAppliance.Cost), tContracts.CurrentValue = (tContracts.ContractValue+tAppliance.Cost), tContracts.QTRZero = 'S', tContracts.LastQTRWritten = 'N', tContracts.QTRPay1 = 'N', tContracts.QTRPay2 = 'N', tContracts.QTRPay3 = 'N', tContracts.QTRPay4 = 'N' 
dbo.tContracts 
LEFT JOIN dbo.tAppliance ON tContracts.ContractNo = tAppliance.ContractNO 
WHERE tContracts.ContractNo=('"& ContractNo &"')

Harsh Athalye
India.
"Nothing is Impossible"

Gyto
Posting Yak Master

144 Posts

Posted - 2006-09-28 : 12:02:09

ahaaa.....thanks.....I'll give that a try.....was getting a bit mixed up coz I'm effectively copying it accross from Access to SQL Server (with some modifications, obviously) and it works that way in Access..... =S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 12:36:28

Access has slightly different systax on Update with join

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources