SQL novice needs update query help 1

[grecian9]

How do I run an update query in a field that is varchar? I'm trying to set the values in field "metricvalue" to a new value but my guess is that you can't multiply varchar values? This is my syntax:

update tblmetricdata
set metricvalue = metricvalue * 1.5
where metricid between 119 and 130
and modelid = 282

which gives this error message. "Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated."

Thanks for any help )

 

[LFCfan]

is metricvalue or metricid the varchar field?

Check out SQL function CONVERT in BOL, for example
*********
declare @var varchar(4)
set @var = '1234'
declare @int int

set @int = convert(numeric(4,0), @var)
***********

 

[SQLSister]

Is there some compelling reason not to make this a numeric field?

 

[MeanGreen]

While I agree with SQLSister, you could do what you want as long as the values contained within your Metricvalue will convert to numeric value. So you probably want to run a query to identify those rows where metricvalue is not numeric to determine what you need to do with the data.

select * from tblmetricdata
where isnumeric(metricvalue) = 0


Otherwise, just run your query as stated:

update tblmetricdata
set metricvalue = metricvalue * 1.5
where metricid between 119 and 130
and modelid = 282
and isnumeric(metricvalue) =

Hope this helps.

 

[SQLSister]

When I was a management analyst, I had to extract data from thousands of databases. In doing so I found that one of the key problems causing databases to return incorrect information was poor database design, especially using character fields where numbers or date data would be stored and failure to set up proper relationships. As a dba and programmer, I have found that using the wrong data type invariably means that you will have to write more complex code and more of it to make things work properly. Yes, you can use the workarounds to fix this problem, but it is better to fix the source of the problem than keep fixing the symptoms. Being realistic, I understand that it may not be possible to change the data type if a lot of code has already been written, but changing it should be the first thing you consider.

 

[grecian9]

It's metricvalue that is varchar. It can't be numeric because it is storing text fields as well.

 

[grecian9]

FYI my problem has been sorted using the following code:

update tblmetricdata
set metricvalue = cast( metricvalue as float ) * 1.5
where metricid between 119 and 130
and modelid = 282

It's the cast function that does it.

Thanks for your replies.

 

[MeanGreen]

grecian9,
The "isnumeric" function will take a varchar field and verify that it can be CAST or CONVERTED to a numeric value. Within SQL 7, you do not have to CAST your varible as FLOAT when you multiply as SQL will do this for you, provided the metricvalue will be numeric and as long as your multiplicative is a float value (like 1.5) it will use float. If your update worked without error, then I assume you did not have any issues with metricvalue. When you run into errors, you will need a method to find the typo, and this is the query to find all metricvalue columns that cannot be CAST into float values:

select * from tblmetricdata
where isnumeric(metricvalue) = 0

Hope this helps.

 

[grecian9]

I'm not sure if understand but I can tell you this; the ISNUMERIC syntax above brings up the metricvalue data that wouldn't work with my original query. Thus, I'm not sure if what you are saying is right as it does seem to require a CAST and FLOAT to work? I'm working with SQL server 2000 by the way.