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

[z28quetzal]

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

[marist89]

What happens with plant 002? Do you want to restart your counter at 1?

[z28quetzal]

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

[padders]

What's wrong with stored procedures anyway?

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>

[z28quetzal]

Duh! Simple enough.

Thank you for the example.


joe