We help IT Professionals succeed at work.

Update the sequnce number in table

Hi,

I need to a update statement which will update the SEQ_NUM column based on the FIELD1 column. The data needs to be updated like the below format.

FIELD1	SEQ_NUM
----------------
AAA	1
AAA	1
AAA	1
AAA	1
BBB	2
BBB	2
CCC	3
CCC	3
CCC	3
DDD	4
DDD	4
EEE	5

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
See if this is what you need.
drop table tab1 purge;
create table tab1(field1 char(3), seq_num number);

insert into tab1 values('aaa',0);
insert into tab1 values('aaa',0);
insert into tab1 values('aaa',0);
insert into tab1 values('bbb',0);
insert into tab1 values('ccc',0);
insert into tab1 values('ccc',0);
commit;


merge into tab1 t1
using
	(
	select rowid myrowid,field1, dense_rank() over(order by field1) myrank from tab1
	) t2
on (t1.rowid=t2.rowid)
when matched then update set seq_num=myrank
/

Open in new window

hi,

Ya your understanding is correct. But I have created a sequence here to increment the seq_num. How can i use the SQL sequnce here to update seq_num column.

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What Oracle version?
i am using Oracle 10g
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Same table as above.  Added a sequence to the test.
drop sequence myseq;
create sequence myseq;

declare
	myCurrVal number;
begin
for i in (select distinct field1 from tab1) loop
	select myseq.nextval into myCurrVal from dual;
	update tab1 set seq_num=myCurrVal where field1=i.field1;
end loop;
commit;
end;
/

select * from tab1;

Open in new window

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Just curious as to why you would want to do that. Generally, a sequence is used to insure or create uniqueness, which is not the case here.
GOOD

Explore More ContentExplore courses, solutions, and other research materials related to this topic.