-
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
-
What happens with plant 002? Do you want to restart your counter at 1?
Jeff Hunter
-
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
-
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>
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|