CODEWITHSUNDEEP
sqlsql-serversql-update
jason
jason

Reputation: 7164

Error in SQL UPDATE CLAUSE

I have this simple SQL query : 

  UPDATE [mydb].[dbo].[EXPLANATIONS] 
  SET [EXPLANATION] = " This is the new explanation." 
  WHERE [RECORDNUMBER] = 123456 
    AND [EXPLANATIONNUMBER] = 7;

and I get this error :

Msg 207, Level 16, State 1, Line 1
Invalid column name ' This is the new explanation.' .

How can I get rid of this error and update the row correctly? Thanks.

Upvotes: 1

Views: 139

Answers (3)

Neeraj Kumar Yadav
Neeraj Kumar Yadav

Reputation: 411

We can update below table using double quotes as well, just add set statement above the query.

set QUOTED_IDENTIFIER  off

UPDATE [mydb].[dbo].[EXPLANATIONS] 
  SET [EXPLANATION] = "This is the new explanation."
  WHERE [RECORDNUMBER] = 123456 AND [EXPLANATIONNUMBER] = 7;


set QUOTED_IDENTIFIER  on

Upvotes: 1

SMA
SMA

Reputation: 37023

Instead of using double quotes, use single quotes like:

SET [EXPLANATION] = '<i>new explanation</i>'

Upvotes: 1

Brave Soul
Brave Soul

Reputation: 3620

Double quotes are usually used to object names (e.g. column name). That is part of SQL-92 standard.

In ANSI SQL, double quotes quote object names (e.g. tables) which allows them to contain characters not otherwise permitted, or be the same as reserved words (Avoid this, really).

single quotes to the string litral

  UPDATE [mydb].[dbo].[EXPLANATIONS] 
  SET [EXPLANATION] = 'This is the new explanation.'
  WHERE [RECORDNUMBER] = 123456 AND [EXPLANATIONNUMBER] = 7;

Upvotes: 5

Related Questions