CODEWITHSUNDEEP
sql-server-2008sql-update
SomeoneRandom
SomeoneRandom

Reputation: 264

SQL Update statement - syntax error near "SET"?

I am generating the sql statement below based on some coldfusion logic, but it is erroring and I can't seem to find the cause, I have tried making many different modifications to it and nothing seems to be helping.

UPDATE MAIN_RECORDS 
SET JONUM = NULL, 
SET CUSTNAME = 'Super Sweet Name', 
SET CONTACTDT = 02/28/2011, 
SET ENGRECDT = 03/07/2011, 
SET HOW_WR_DT = 03/07/2011, 
SET COMM_DT = 03/29/2011, 
SET FACAVALDT = NULL, 
SET FAX_SUPDT = 03/07/2011, 
SET LINENUM = 'CLPRO L6', 
SET POLENUM = 'CLPRO 125 T T3', 
SET REASON = '03/07/11 NO VAC FAC THIS IS THE WRONG INFORMATION IT WAS ON HERE TWICE', 
SET REC_TYPE = 'H', 
SET ORDER_TYPE = 'P', 
SET CANCEL_ORDER = 'Y', 
SET State_abbr = 'IL', 
SET dbfk_state = 17, 
SET xx_streetnumber = '2626', 
SET xx_street = 'Fake St', 
SET xx_city = 'NEWTON', 
SET xx_class_of_service_ind = 'R', 
SET xx_cellphone_ind = '1', 
SET xx_assigned_phone = '3045653897', 
SET xx_exchange_name = 'NEWTON', 
SET XX_new_ref_code = '60', 
SET xx_new_service_type = '11', 
SET ORD_COMDT = 03/11/2011, 
SET delivery_date = NULL 
WHERE ordernum = '08824112' AND exchnum = '304565'

Currently the error that management studio is giving me is:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.

Upvotes: 3

Views: 23061

Answers (4)

Vignesh
Vignesh

Reputation: 1

Only one SET is needed in update Keyword to update n number of columns - example:

Update Employee 
set City = Chennai,Country ='India',Employee name = 'Vignesh'
where  Employee Id = 1X234

Upvotes: 0

samack
samack

Reputation: 815

Well generally the command syntax for this would follow this logic

UPDATE table_name
 SET column1=value, column2=value2,...
 WHERE some_column=some_value

so only one SET not the multiple that you have

Upvotes: 3

user166390
user166390

Reputation:

Look at the UPDATE statement. The syntax in the post is all wrong :)

The relevant portion:

SET
        { column_name = { expression | DEFAULT | NULL }
        | @variable = expression
        | @variable = column = expression } [ ,...n ]

Note that SET can only be specified once. The ,...n signifies the previous consuct (that in the {}) can be specified an additional n times, separated with a comma: the SET keyword itself, however, is outside that construct.

Happy coding.

Upvotes: 5

PaulStock
PaulStock

Reputation: 11283

You only need 1 SET statement, instead of the multiple ones you have.

Also, your dates need to have single quotes around them.

e.g.:

UPDATE MAIN_RECORDS 
SET JONUM = NULL, 
    CUSTNAME = 'Super Sweet Name', 
    CONTACTDT = '02/28/2011', 
    ENGRECDT = '03/07/2011', 
    HOW_WR_DT = '03/07/2011', .....

Upvotes: 11

Related Questions