Nested For Loop update statement (pl/sql) 1

[soushi01]

Hi,

Need advise that I need to update number / adding number for one of column by using Nested for loop statement but i have no idea how to write it something like this

SQL> BEGIN
2 FOR i IN 1..2 LOOP
3 FOR j IN 1..4 LOOP
4 DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' ||
5 i || ' Inner Loop counter is ' || j);
6 END LOOP;
7 END LOOP;
8 END;
9 /

Here is original record

table Purchase

Customer_ID -- Purchase_no -- item_shoe
---1----------------- P001 ---------- Nike
---1----------------- P001 ---------- Addidas
---2----------------- P002 ---------- Puma
---3----------------- P003 ---------- Power
---3----------------- P003 ---------- Cheetah
---3----------------- P003 ---------- New Balance

i need update number like this below

Customer_ID -- Purchase_no -- item_shoe
---1----------------- P001-1 ---------- Nike
---1----------------- P001-2 ---------- Addidas
---2----------------- P002-1 ---------- Puma
---3----------------- P003-1 ---------- Power
---3----------------- P003-2 ---------- Cheetah
---3----------------- P003-3 ---------- New Balance


thanks

 

[PHV]

A starting point:

SELECT A.Customer_ID, A.Purchase_no || '-' || Count(*), A.item_shoe
FROM Purchase A INNER JOIN Purchase B ON A.Purchase_no=B.Purchase_no AND A.item_shoe>=B.item_shoe
GROUP BY A.Customer_ID, A.Purchase_no, A.item_shoe

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[soushi01]

Hi, PHV

Thanks u so much for ur solution. It works