Add 1 to integer Field

Question

Post reply

Add 1 to integer Field

garethmann101

SSCommitted

Points: 1980
More actions
September 23, 2008 at 8:05 am

#191224

Hi,
I am trying to add one to an integer field, seems pretty simple right? Only I dont know how to get a value from a field. Using just the field name throws up an error ("Arithmetic overflow error converting numeric to data type numeric")
Here is my code ('myColumn' is the name of the column that I want to add 1 to):
update tbCities08
set myColumn= myColumn + 1
where Unique_ID = 020001000080

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Glen SSCertifiable Points: 6737 More actions · Answer 1

Glen

SSCertifiable

Points: 6737

September 23, 2008 at 8:10 am

#875677

Can you please provide a DDL for the table?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Gareth

A really useful habit to get into is to always SELECT whatever you're intending to UPDATE first. If you run a simple SELECT...

SELECT myColumn

FROM tbCities08

WHERE Unique_ID = 020001000080

...you will probably find that your Unique_ID column isn't numeric, it's character (it's left-padded with a 0), and you've supplied the value as numeric. You need to put quotes around it -

'020001000080'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

garethmann101 SSCommitted Points: 1980 More actions · Answer 3

Man, that was in fact the problem, no single quotes around the ID field value (varchar field) for the "where" clause.

Thanks for that, how ironic that there was nothing wrong with my "add 1" syntax, even though the error message seemed to suggest that that was where the problem lied.....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

You're welcome Gareth, many thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden