asked on

How to increment by one in SQL table

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!

Joachim Carrein

Hello printmedia,

Regards,

joachimcarrein

Joachim Carrein

sorry, my text went missing

try something like

update myTable update key=key - 1000 + 10000

Joachim Carrein

ok, something is wrong with me today :s

this should be set instead of second update

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

printmedia

ASKER

That's pretty much it, but what should I change the Data Type to, it is currently int.

Guy Hengel [angelIII / a3]

>but what should I change the Data Type to, it is currently int.
no need to change, int supports values much larger...

UnifiedIS

int is big enough

Joachim Carrein

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

printmedia

ASKER

Thanks!