asked on

Update the sequence to multiple rows

Hello Experts,

I want to update multiple row with same sequence.

Here is my object:

create table mytab (id number, name varchar2(400), source varchar2(400), key_val number );

insert into mytab
select 1 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 2 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 3 as ID, 'name2' as name, 'source2' as source , null as key from dual
union
select 4 as ID, 'name3' as name, 'source3' as source , null as key from dual
;
commit;

create sequence key_seq  increment by 6666 start with 1;

Open in new window

Now if I run my select statement then I can see that the first and second row have the same values so I need to update the key column with same sequence number to make a track.

select 
id, name, source, key_val,dense_rank() over (order by name, source) dr 
from mytab;

Open in new window

Result:

1	name1	source1		1
2	name1	source1		1
3	name2	source2		2
4	name3	source3		3

Open in new window

Now the expected result would be getting same sequence to row 1 and 2 and follow nextval to other 2 rows.

flow01

You expect it, but I don't see the logic row 3 and 4 should be getting the same sequence: can you explain please?

slightwv (䄆 Netminder)

I agree that I do not understand what you are trying to do.

My guess is you want to populate existing key_val's on current dense_rank and then continue with all NEW inserts?

You'll need to alter your sequence to start with something not already assigned instead of starting with 1.

If that is what you are wanting, you'll need a MERGE to fix all current rows and a trigger to check for already existing key_vals on insert.

drop table mytab purge;
create table mytab (id number, name varchar2(400), source varchar2(400), key_val number );

insert into mytab
select 1 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 2 as ID, 'name1' as name, 'source1' as source , null as key from dual
union
select 3 as ID, 'name2' as name, 'source2' as source , null as key from dual
union
select 4 as ID, 'name3' as name, 'source3' as source , null as key from dual
;
commit;

drop sequence key_seq;
create sequence key_seq  increment by 6666 start with 6666;

--fix all existing key_vals
merge into mytab m
using (
	select id,dense_rank() over (order by name, source) dr from mytab
) x
on (m.id=x.id)
when matched then
	update set key_val=dr
/


create or replace trigger mytab_trig
before insert on mytab
for each row
declare
	v_key_val number;
begin
	begin
		select key_val into v_key_val from mytab where name = :new.name and rownum=1;
		exception when no_data_found then
			v_key_val := key_seq.nextval;
	end;
	:new.key_val := v_key_val;
end;
/

show errors
	
--should reuse the original key_val
insert into mytab(id,name,source) values(5,'name1','source1');
--should get a new one
insert into mytab(id,name,source) values(6,'name4','source4');


--make sure everything is correct
select * from mytab;

Open in new window

Swadhin Ray

ASKER

Sorry if I was not clear . So the key field should be updated with sequence 6666 for row 1 and row 2 and 6667 for row 3 and 6668 for row 4.

I wan to use them on a trigger so that if I insert similar rows in future then the key field should be inserted automatically on every insert.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swadhin Ray

ASKER

@slightwv : your response and my comment came nearly same time, so after my reply i checked your comments.
You are absolutely right and your solution works.

Thanks again.