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>
Reply With Quote