DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to 'resequence' a column value with an update statement...

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question How to 'resequence' a column value with an update statement...

    Hi:

    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 7
    001 item1 s004 2222 2
    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, incrementing by the value of 1. The end result should look like this:

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

    Can this be done with a SQL update statement? The rows being updated may or may not be 'in sequence' by rank. That is why I want to be able to resequence the ranking, starting at 1 and incrementing by 1.

    thanks for any help...

    joe

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What happens with plant 002? Do you want to restart your counter at 1?
    Jeff Hunter

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    resequence...

    Let me elaborate on this a little, I guess I did not make myself clear enough:

    Resequencing applies to plant/item rows only. That is the key to the table. So this would be the original example:

    plant item supplier part rank
    001 item1 s001 1234 6
    001 item1 s002 1111 7
    001 item1 s004 2222 2
    001 item1 s005 4445 5

    there could be another example like this:

    plant item supplier part rank
    001 item2 s001 1234 5
    001 item2 s011 6666 3
    001 item2 s004 2222 1

    What I am asking is given a plant/item, how can I resequence the 'rank' column with an update statement. Looks like this can't be done. If I do somethingl like:

    update tablexxx
    set rank = rank + 1
    where plant = '001'
    and item = 'item2'

    Then I would get the current value plus 1. That is not good because my goal is to have the rows look like this:

    plant item supplier part rank
    001 item2 s001 1234 3
    001 item2 s011 6666 2
    001 item2 s004 2222 1

    That is why I was asking here if anyone knows of a way to do this. I was just trying to keep from having to write a stored proc for this...

    joe

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    What's wrong with stored procedures anyway?
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE TABLE table_name (
      2    plant CHAR (3),
      3    item CHAR (10),
      4    supplier CHAR (10),
      5    part CHAR (10),
      6    RANK NUMBER);
    
    Table created.
    
    SQL> INSERT INTO table_name VALUES ('001','item1','s001','1234',6);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item1','s002','1111',7);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item1','s004','2222',2);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item1','s005','4445',5);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item2','s001','1234',5);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item2','s011','6666',3);
    
    1 row created.
    
    SQL> INSERT INTO table_name VALUES ('001','item2','s004','2222',1);
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT plant, item, supplier, part, rank
      2  FROM   table_name;
    
    PLA ITEM       SUPPLIER   PART             RANK
    --- ---------- ---------- ---------- ----------
    001 item1      s001       1234                6
    001 item1      s002       1111                7
    001 item1      s004       2222                2
    001 item1      s005       4445                5
    001 item2      s001       1234                5
    001 item2      s011       6666                3
    001 item2      s004       2222                1
    
    7 rows selected.
    
    SQL> UPDATE table_name t
      2  SET    rank = (
      3         SELECT COUNT (*)
      4         FROM   table_name 
      5         WHERE  plant = t.plant 
      6         AND    item = t.item 
      7         AND    rank <= t.rank);
    
    7 rows updated.
    
    SQL> SELECT plant, item, supplier, part, rank
      2  FROM   table_name;
    
    PLA ITEM       SUPPLIER   PART             RANK
    --- ---------- ---------- ---------- ----------
    001 item1      s001       1234                3
    001 item1      s002       1111                4
    001 item1      s004       2222                1
    001 item1      s005       4445                2
    001 item2      s001       1234                3
    001 item2      s011       6666                2
    001 item2      s004       2222                1
    
    7 rows selected.
    
    SQL>

  5. #5
    Join Date
    Mar 2004
    Posts
    3

    Thank you

    Duh! Simple enough.

    Thank you for the example.


    joe

Posting Permissions

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


Click Here to Expand Forum to Full Width