sql - Update one field in a table in reference to another field on the same table Oracle -

i need update varchar2 field sequential values e.g 001, 002, 002 in reference field entity_id on same table. need such if example have same entity_id on 2 different rows, sequential values should same.

an example output:

entity_id      seq_field 1234           001 1234           001 4567           002 4567           002 3412           003 

i have tried rownum gives different values each entity_id , of course values not have trailing zero. please help.

merge the_table using (   select rowid rid,          entity_id,           to_char(dense_rank() on (order entity_id), 'fm00000') seq   foo ) t on (the_table.rowid = t.rid) when matched    update set seq_field = t.seq; 

if want start new sequence each entity_id need change statement slightly:

merge foo using (   select rowid rid,          entity_id,           to_char(row_number() on (partition entity_id order null), 'fm00000') seq   foo ) t on (foo.rowid = t.rid) when matched    update set seq_field = t.seq; 

note used row_number() instead of dense_rank() , partition entity_id restart numbering each new value of entity_id. if have column determine "order" 1 entity_id, can replace null in order null column e.g. order created_at