?
Solved

How to increment by one in SQL table

Posted on 2008-01-24
9
Medium Priority
?
5,547 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!
0
Comment
Question by:printmedia
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:Joachim Carrein
ID: 20737682
Hello printmedia,



Regards,

joachimcarrein
0
 
LVL 4

Expert Comment

by:Joachim Carrein
ID: 20737700
sorry, my text went missing

try something like

update myTable update key=key - 1000 + 10000
0
 
LVL 4

Expert Comment

by:Joachim Carrein
ID: 20737713
ok, something is wrong with me today :s

this should be set instead of second update
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20737731
so you just want to add 9000 to the actual values?:
UPDATE yourtable SET Ref_No = Ref_No + 9000

Open in new window

0
 

Author Comment

by:printmedia
ID: 20737757
That's pretty much it, but what should I change the Data Type to, it is currently int.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20737798
>but what should I change the Data Type to, it is currently int.
no need to change, int supports values much larger...
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20737807
int is big enough
0
 
LVL 4

Expert Comment

by:Joachim Carrein
ID: 20737859
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
0
 

Author Comment

by:printmedia
ID: 20737961
Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question