Skip to main content
T
trojnfn
Inspiring
February 7, 2009
Question

Procedure to increment

  • February 7, 2009
  • 2 replies
  • 539 views
I have a table with a recodID, partNumberID, partNumber. The tabbe looks like this :
1 01 12345
2 01 99999
3 02 xxxxx
4 03 mmmm
5 04 98333
6 04 848344

The record id is unique and is 1,2,3 etc., the part number id can be the same so if there are two part numbers, they both have the same part number id. What I need to do now is add another column to the table called line item. So my above tabel example with the line item would look like this, part numbers with the saem partID would have line items 1,2, etc :
1 01 12345 1
2 01 99999 2
3 02 xxxxx 1
4 03 mmmm 1
5 04 98333 1
6 04 848344 2

My question is how do I insert the line item numbers so that the increment correctly ?
    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    February 7, 2009
    It looks like a bad design. What does a record represent, given that it can have two part numbers which may or may not be the same?
    A
    Anonymous
    February 7, 2009
    It's not an entirely bad design. I use the same process for managing item orders for my content management system. For example, 5 items could be under a parent ID and each of those children have an order of 1 to 5. The user can then rearrange the item order which simply updates the item order number.

    While my design is similar, it does have a different purpose as I have a parent ID to work with which makes this a lot easier.

    I have given some ideas a go to get your problem sorted but as yet have not come to a solution (I'm really not that good). I imagine it would involve some clever SQL, using a variation of COUNT() and DISTINCT() with things like GROUP BY.

    As I said...I'm working on some queries at the moment, but keep pushing yours.

    You basically need a way to COUNT how many items each belong to the partNumberID then loop that count for those items updating the new column with the currentRow variable.

    Hmm, battling on...
    Mikey.
    T
    trojnfnAuthor
    Inspiring
    February 8, 2009
    My initial thought was to count the total number of partNumberID's and that will give me the max line items for that id. I think I would then have to loop from 1 to the max number, and set the line item to line item + 1 (since the line items will initially be zero), so that should give me 1, 2, 3, etc.....but what I am not sure of is how to switch ids and start the process over again. So if my first pass for the id contains three, it woud update to line item 1, 2, then 3, then switcth to the next id ? I am not sure how to do that.
    D
    Dan_Bracuk
    Inspiring
    February 7, 2009
    Is this a db table or an html table for displaying some query results?
    T
    trojnfnAuthor
    Inspiring
    February 7, 2009
    It is a table in sql server. The data already exists. The line item column was just added and currently is NULL. I just need to know how to add (increment) the line item numbers for each part number and part number id combination, like my example.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices