Incorrect syntax near the keyword 'set' and 'as'

Question

Hi i am getting exception in my mssql code. Here is the exception:

Incorrect syntax near the keyword 'set' and 'as'.

And here is the my sql code:

UPDATE SET TBLSurvey.Status=-1  FROM TBLSurvey
RIGHT JOIN (
SELECT  OrderId
FROM [CRM_NEW].[dbo].[TBLSurvey]
GROUP BY OrderId HAVING(COUNT(*)>1)) AS sd ON TBLSurvey.OrderID=sd.OrderId
WHERE AnswerDate IS NULL AND Status=0‏

Have no idea.

Well, where did you learn that syntax from? Did you just make it up? — Aaron Bertrand, Dec 06 '13 at 15:12
This is not how you work with software, making stuff up on the fly. Your issue is exactly why [they write documentation and make it freely available](http://technet.microsoft.com/en-us/library/ms177523.aspx), and why questions and answers on this site remain available for you to learn from (e.g. [this one](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server/1604212#1604212)). Making up syntax and then throwing your hands in the air when it doesn't work is not the kind of behavior anyone on this site wants to encourage - don't you want to learn for yourself? — Aaron Bertrand, Dec 06 '13 at 15:17
You are right Aaron. Looking sql documentation and tutorial now. I want to delete this post but cant. — ftdeveloper, Dec 06 '13 at 15:20
I've rolled back your question because it stopped making sense in view of the answers already posted (including the one you've accepted). It's all right to have and ask many questions, but please stick with reading the manuals for a while till you are sure you've hit an obstacle you can't overcome. At that point, please feel free to come back with a **new** question (do not just edit an old one). — Andriy M, Dec 06 '13 at 15:42
Yeah, can op delete this qn? It's useless for users and punishes @fuat for no reason. — Robino, Sep 18 '14 at 19:50

score 3 · Accepted Answer · answered Dec 06 '13 at 15:14

3

In SQL Server, the update should start:

update TBLSurvey
    set Status = -1
    from TBLSurvey . . .

The syntax for update in SQL Server is here. If I had to guess, it looks like you are confused with aspects of MySQL syntax.

answered Dec 06 '13 at 15:14

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered Dec 06 '13 at 15:14

2

Perhaps you meant:

UPDATE t
  SET [Status] = -1
  FROM dbo.TBLSurvey AS t
  WHERE [Status] = 0
  AND NOT EXISTS
  (
    SELECT 1 FROM CRM_NEW.dbo.TBLSurvey
    WHERE OrderId = t.OrderId
    GROUP BY OrderId HAVING COUNT(*) > 1
  ) AS sd;

answered Dec 06 '13 at 15:14

Aaron Bertrand

272,866
37
466
490

Incorrect syntax near the keyword 'set' and 'as'

2 Answers2