Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: I need help with an update statement - please help!!


    Would anyone please give me some help on this? I have a table, it contains several columns, the most important are these:

    plant - char(3)
    item - char(10)
    supplier - char(10)
    part - char(10)
    rank - number

    My question is this: how can I write an update statement that would update the field 'rank' in a sequential manner? Take this example (the columns would match the layout above).

    001 item1 s001 1234 6
    001 item1 s002 1111 3
    001 item1 s004 2222 1
    001 item1 s005 4445 5

    What I want is to be able to resequence the last field (rank) starting with the value 1 and clearing up any 'gaps' in the process. the end result should look like this:

    001 item1 s001 1234 4
    001 item1 s002 1111 2
    001 item1 s004 2222 1
    001 item1 s005 4445 3

    Can this be done with a SQL update statement?

    thanks for any help...


  2. #2
    Join Date
    Jul 2003
    you do not specify the rule for ordering the last column so I don't see how we can help you with this limited information.

    For one, why do you need to re-rank them if they are already in order?

    this should work when you want to see them ranked without changing
    the 'rank' column at all:

    PHP Code:
    select plantitemsupplierpartrownum rank from (
    select plantitemsupplierpart
    from tablename
    order by rank desc
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004

    reason for ranking...

    Ooops! Let me give more information.

    What I left out is that there are times when a new row will be added for a given plant/item/supplier combination and it will be ranked '1'. So the 'new' row will be going in as rank '1'. Therefore all the existing rows will need to be ranked at the current value plus one.

    There might be times when a 'gap' will be in the existing rows. That is why you saw in the original example ranks 1, 3, 5 and 6.

    As far as the rule for reordering the last column that would be starting at the lowest value (1) and incrementing this by 1.

    any info would be appreciated...

  4. #4
    Join Date
    Jul 2003
    I recommend using precision with the current rank column.
    If something new comes in that should be the new #1 then insert it with .99 or something.
    Then run the query I gave you earlier.

    Seems like you want to waste a lot of time writing code for something that could be handled simply.

    Anyways, you already answered your own question since you want to increment all the 'rank's by one:

    update tablename
    set rank = rank+1;

    insert into tablename values ('x','x','x',1);

    I must be missing some more information.
    You can have the gaps and still get what you want from the data.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts