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?

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.

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.

LVL 43

ste5anSenior DeveloperCommented: 2018-11-15

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: 2018-11-15

That is really helpful thank you!

Access to SQL Syntax (Update query)

Who is Participating?