Error in SQL UPDATE CLAUSE

Question

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.

Use the single quote. Instead of the " use ' So "This is the new explanation." this becomes 'This is the new explanation.' — Mahesh
– Mahesh, Commented Mar 13, 2015 at 7:37

Brave Soul · Accepted Answer · 2015-03-13 07:39:57Z

5

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;

answered Mar 13, 2015 at 7:39

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SMA · Accepted Answer · 2015-03-13 07:40:28Z

1

Instead of using double quotes, use single quotes like:

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

answered Mar 13, 2015 at 7:40

SMA

37.2k8 gold badges53 silver badges76 bronze badges

Comments

Flexo - Save the data dump · Accepted Answer · 2016-04-07 08:05:52Z

1

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

edited Apr 7, 2016 at 8:05

Flexo - Save the data dump♦

89.1k22 gold badges205 silver badges286 bronze badges

answered Apr 18, 2015 at 5:46

Neeraj Kumar Yadav

4114 silver badges15 bronze badges

Collectives™ on Stack Overflow

Error in SQL UPDATE CLAUSE

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related