Access to SQL Syntax (Update query)

Trying to convert Access syntax to SQL (Update Query)

UPDATE dbo.tbl_DriveSalesSidhilMatrix
Set tbl_DriveSalesSidhilMatrix.EnteredBy = case when OperatorName is null then Operator else OperatorName
From dbo.view_Z_SalesOrderAmmendmentsSums INNER JOIN
                         dbo.tbl_DriveSalesSidhilMatrix ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_DriveSalesSidhilMatrix.EnteredBy LEFT OUTER JOIN
                         dbo.tbl_Syspro_Operators ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_Syspro_Operators.OperatorCode
WHERE (dbo.tbl_DriveSalesSidhilMatrix.RecordSource = N'Sidhil')

I'm getting "Incorrect syntax near the keyword 'From'."

Can anyone help?
HKFueyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Format your code. This is the first step in solving syntax errors.

Then: Use always table alias names and prefix all columns with it. It's unclear to which source Operator and OperaterName belong.

The error is the missing END for the CASE expression. E.g.

UPDATE DSSM
SET    DSSM.EnteredBy = CASE WHEN SO.OperatorName IS NULL THEN SOMS.Operator
                             ELSE SO.OperatorName
                        END
FROM   dbo.view_Z_SalesOrderAmmendmentsSums SOMS
       INNER JOIN dbo.tbl_DriveSalesSidhilMatrix DSSM ON SOMS.Operator = DSSM.EnteredBy
       LEFT JOIN dbo.tbl_Syspro_Operators SO ON SOMS.Operator = SO.OperatorCode
WHERE  DSSM.RecordSource = N'Sidhil';

Open in new window


But in this case (pun intended) we don't use CASE. Either the ANSI-SQL compatible COALESCE() or the T-SQL specific ISNULL() function. E.g.

UPDATE DSSM
SET    DSSM.EnteredBy = ISNULL(SO.OperatorName, SOMS.Operator)
FROM   dbo.view_Z_SalesOrderAmmendmentsSums SOMS
       INNER JOIN dbo.tbl_DriveSalesSidhilMatrix DSSM ON SOMS.Operator = DSSM.EnteredBy
       LEFT JOIN dbo.tbl_Syspro_Operators SO ON SOMS.Operator = SO.OperatorCode
WHERE  DSSM.RecordSource = N'Sidhil';

Open in new window

p.s. embed you code into [code] tags (the CODE button) in all your posts, this increases readability and makes copying code easier.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKFueyAuthor Commented:
That is really helpful thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.