• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

SQL Query auto increment

I need help with creating an auto increment number starting at a specifc range and length of digits.


000200    Joe
000201    Abbey
000202    Mike
000203    Jeff

I need the 6 digit number to start at 200 and increment throughout the list.
  • 3
  • 3
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, autoincrement can start at any value.
however, the "length" is a formatting issue, and not a data issue, at least conceptually speaking.

you have 3 options:
* start from 1000200 instead of 200, and return only the last 6 digits
* start from 200, and left-pad with leading 000
* you don't use a auto-number field, but a text field, and build the "sequencing" yourself (somehow). not recommended

in all 3 options, you likely have to consider the case when you get over the 999999 value ... you shall come to 1000000, or back to 000000, which both will result in other problem.

take this tip: don't do it. just put a numerical autonumber, and that should be ok.

holemaniaAuthor Commented:
Hi angelIII,

The list will never have more than 10,000 records.  Over the course of 16 years of the database being utilized, we have about 3000 vendors in this list.  I need to auto number the vendor ID since how it was originally created, there's no restriction on Vendor ID.  So numeric, alpha, and alphnumeric is use.  I want to standardize the vendor ID to a 6 digit starting from 200 as my starting number.

After playing with it this is what I come up with.


It seems to work.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
only that ROW_NUMBER() will depend on the name's list.
so, if you add a new vendor in the list somewhere, the row_number() would change.
but I assume that you just want to update the existing list with this?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

holemaniaAuthor Commented:
Yes that is correct.  I just need to update the existing list.  
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this article explains how to formulate UPDATE with JOINS:

which is what you need to do, because you cannot update directly with row_number() function ...
holemaniaAuthor Commented:
Thanks AngelIII.  Since you were the only one that responded, I have given you points.  What you pointed out is very good tip.  

For what I need to do, I think the Row_Number will work for me since I just need to randomly reassign 6 digits to a list of vendors that I need to rename the Vendor IDs to.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now