-1
UPDATE kpi.data
SET MetricValue = ''
WHERE (MetricValue IS NULL ) 
  and PeriodDate  =  '2020-01-02'
     and ReportID = 4

I got this error

Msg 8114, Level 16, State 5, Line 4 Error converting data type varchar to numeric.

James Z
  • 12,209
  • 10
  • 24
  • 44
Daniel Okoh
  • 1
  • 1
  • SQL Server (MS SQL) <> MySQL – Akina Dec 06 '22 at 08:42
  • *I tried to set null to blank but got this error because the data is of the numeric datatype.* Your output column must be of char type, not numeric one. – Akina Dec 06 '22 at 08:43
  • The sql way of indicating there is no value is NULL , can you explain why you want to do this given there are ways of dealing with null eg coalesce in selects etc.. – P.Salmon Dec 06 '22 at 09:18
  • Noway to store string value in numeric column. – Akina Dec 06 '22 at 11:00

1 Answers1

0

What you are trying to do does not make any sense, so it cannot work, and it will not work.

  • If the data type of a column is numeric then the column can only contain numbers.
  • If the data type of a column is numeric and nullable the column can contain either a number or null.

There is no other value that a numeric field can receive, either in MySQL or in any other relational database that I have ever heard of.

Perhaps what you want to do is to convert null to blank when selecting, (not when inserting/updating,) in which case you should look at some other Q&A like this one: MySql Query Replace NULL with Empty String in Select

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142