create table re (
pk1col1 number,
pk1col2 number,
paid number,
constraint pk_re primary key (pk1col1, pk1col2)
);
create table p (
fk1col1 number,
fk1col2 number,
trype char(2),
constraint fk_p_re foreign key (fk1col1, fk1col2) references re
(pk1col1, pk1col2)
);
create table pt (
fk1col1 number,
fk1col2 number,
amt number,
constraint fk_pt_re foreign key (fk1col1, fk1col2) references re
(pk1col1, pk1col2)
);
update re
set paid =
(select re.paid + decode(p.rtype,
'xx', (-1 * :pt_amt),
'yy', (-1 * :pt_amt),
:pt_amt)
from p
where p.pk1col1 = re.fk1col1 and p.pk1col2 =
re.fk1col2)
where re.pk1col1 = :pt_fk1col1 and re.pk1col2 = :pt_fk1col2;
Now, I want to take the values of bind variables (ie, :pt_*) from the
table pt.
How do I rewrite the update to execute efficiently?
Received on Mon Jul 21 2003 - 14:14:26 CDT
