Access to SQL Syntax (Update query)

HKFuey
HKFuey used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
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.

Author

Commented:
That is really helpful thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial