We help IT Professionals succeed at work.

How to increment by one in SQL table

printmedia
printmedia asked
on
Medium Priority
5,555 Views
Last Modified: 2010-05-18
Hi all.

I have a table (tblRef) that has a column: Ref_No which is a 4 digit number (first one is 1000) as well as other data, I would to be able to make Ref_No 5 digits instead of 4, is there a way to run an update query that will make all of the values in the Ref_No column increment to 5 digits?

 Currently, the field is data type: int, what do I have to change it to so I can insert 5 digits instead of 4.

For example,

Currently Ref_No has the following data:

1000
1001
1002
1003
1004
1005

I would like them to be changed to:
10000
10001
10002
10003
10004
10005

etc...

Any ideas? Thanks you in advance!
Comment
Watch Question

Joachim CarreinSr. Software Developer

Commented:
Hello printmedia,



Regards,

joachimcarrein
Joachim CarreinSr. Software Developer

Commented:
sorry, my text went missing

try something like

update myTable update key=key - 1000 + 10000
Joachim CarreinSr. Software Developer

Commented:
ok, something is wrong with me today :s

this should be set instead of second update
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
so you just want to add 9000 to the actual values?:
UPDATE yourtable SET Ref_No = Ref_No + 9000

Open in new window

Author

Commented:
That's pretty much it, but what should I change the Data Type to, it is currently int.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>but what should I change the Data Type to, it is currently int.
no need to change, int supports values much larger...
int is big enough
Joachim CarreinSr. Software Developer

Commented:
The range for an int is from -2^31 to 2^31-1, that is 32-bit signed.
so from -2,147,483,648 to 2,147,483,647

Author

Commented:
Thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.