ROW_NUMBER()の使い方

DROP TABLE AAA_table04;
CREATE TABLE AAA_table04(
id number(10),
GID VARCHAR2(10),
SPEC VARCHAR2(100),
ITEM_NO VARCHAR2(100),
N_FLG VARCHAR2(100),
BRA_NO VARCHAR2(100)
);
DELETE from AAA_table04;
INSERT INTO AAA_table04 values(1, '', 'AAA', '001', 'OK', '');
INSERT INTO AAA_table04 values(2, '', 'AAA', '002', 'OK', '');
INSERT INTO AAA_table04 values(3, '', 'AAA', '003', 'OK', '');
INSERT INTO AAA_table04 values(4, '', 'AAAB', '002', 'OK', '');
INSERT INTO AAA_table04 values(5, '', 'AAAB', '004', 'OK', '');
INSERT INTO AAA_table04 values(6, '', 'AAAC', '001', '', '');
INSERT INTO AAA_table04 values(7, '', 'BBB', '005', 'OK', '');

select * from AAA_table04;



--------
-- ここから確認
select SPEC, row_number() over (partition by spec order by item_no)
from AAA_table04
;



--UPDATE AAA_table04 main_table SET BRA_NO = sub_table.row_num 
--from 
--(SELECT id, ROW_NUMBER() OVER(partition by spec ORDER BY id) AS row_num FROM AAA_table04) sub_table
--WHERE main_table.id = sub_table.id
--;

UPDATE AAA_table04 SET BRA_NO = 1;
update AAA_table04 a
set BRA_NO= (select group_index
           from (select id,
                 Row_Number() over(partition by spec ORDER BY id) as group_index
                 from AAA_table04) b
          where b.id=a.id);

select * from AAA_table04;