Link to home
Create AccountLog in
Avatar of Steve Berger
Steve BergerFlag for United States of America

asked on

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

Avatar of Steve Berger

ASKER

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
What Oracle version?
i am using Oracle 10g
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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