In Oracle, given the following objects:
create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;
Suppose I populate table a
as follows:
insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);
Using the values in a
, I want to populate tables b
and c
as follows: when a.x
is even, generate a new number from the sequence and insert my_seq.nextval
into b.val
and c.val
; otherwise, insert my_seq.currval
(the most recently generated value but not a new one) into c.val
.
What I currently have is an insert all statement that looks like this:
insert all
when mod(x,2) = 0 then
into b (val) values (my_seq.nextval)
into c (val) values (my_seq.currval)
else
into c (val) values (my_seq.currval)
select x from a
Since Oracle's treatment of sequences in an insert all statement is to generate a new value for each row returned in the subquery, I'm getting 4 newly generated values inserted into c
. How can I attain the behavior I want, 2 newly newly generated values inserted once into b
and twice into c
?