Hi Friends,
I started this thread yesterday but for some reason I'm not able to reply/continue on that old thread. Here are the details from the old thread
I need help with a sql script to update sid_no_new column with values based on sid_no for about 6000+ records.
Here is an example below..
SQL> select * from test1;
SID_NO SID_NO_NEW UNIT_NO
-------------------- -------------------- --------------------
2000 unit1
2000 unit3
2000 unit4
2002 unit16
4500 unit22
In the above example, there are 3 consecutive sid_no's '2000'. Sid_no_new should be 2000_1,2000_2,2000_3 respectively.
It should be like:
SQL> select * from test1;
SID_NO SID_NO_NEW UNIT_NO
-------------------- -------------------- --------------------
2000 2000_1 unit1
2000 2000_2 unit3
2000 2000_3 unit4
2002 2002 unit16
4500 4500 unit22
I ran the below script(thank you for the expert who helped) it works fine for the above records but I'm getting error ORA-30926 when I run it on the table with 6000 records..
merge INTO test1 tgt USING
(SELECT sid_no,
sid_no_new,
unit_no,
COUNT(sid_no) over (partition BY sid_no) cnt ,
row_number() over (partition BY sid_no order by unit_no) rn
FROM test1
) src ON(tgt.sid_no=src.sid_no AND tgt.unit_no=src.unit_no)
WHEN matched THEN
UPDATE SET sid_no_new = CASE WHEN cnt>1 THEN sid_no||'_'||rn ELSE sid_no END;
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Please give your suggestions... THank you so much