This query runs well in mysql but cannot run in mssql. Why?
Don't judge me, I am new in mssql.
UPDATE cvcolumnlist
SET columnindex=columnindex+1
WHERE cvid=40 AND columnindex>=3
ORDER BY columnindex DESC
Error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
You just need to remove ORDER clause. ORDER is pointless in an UPDATE statement.
UPDATE cvcolumnlist
SET columnindex=columnindex+1
WHERE cvid=40 AND columnindex>=3
UPDATE cvcolumnlist
SET columnindex = columnindex+1
WHERE cvid = 40
AND columnindex > 2
this should work
You don't need at all the ORDER BY clause. this is only when SELECTing data from the database.
You may want to note that even though SQL is a language with a specified structure, different SQL engines may parse the query differently and use different syntax since a lot of the SQL functionality is not defined by the language.
for example in mysql you may use the LIMIT keyword for limiting the number of records that are returned and in mssql you may use ROWNUMBER and oracle uses the ROWNUM system.
UPDATE [cvcolumnlist]
SET [columnindex] = [columnindex] + 1
WHERE [cvid] = 40 AND [columnindex] >= 3
I'm not sure if this applies. Most of my queries use something like this.
UPDATE [database].[dbo].[cvcolumnlist]
SET [columnindex] = [columnindex] + 1
WHERE [cvid] = 40 AND [columnindex] >= 3