Re: Ask To Optimize Looping

Hello,

I would try to replace the loop with a single UPDATE FROM Statement:


Update EP_ES06_N_TEMP2
Set ....
FROM (
select
kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as
nilaiygdibagi
from EDP040_07_23
--order by
kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4
) i

WHERE ..


Here a simple example for this syntax:

create table test (i int);
insert into test select * from generate_series (1,20);

update test set i =0
from (select * from generate_series (1,10) s)i
where test.i=i.s;
--Query returned successfully: 10 rows affected


But beware the limitation of update from:

"When a FROM clause is present, what essentially happens is that the
target table is joined to the tables mentioned in the fromlist, and each
output row of the join represents an update operation for the target
table. When using FROM you should ensure that the join produces at most
one output row for each row to be modified. In other words, a target row
shouldn't join to more than one row from the other table(s). If it does,
then only one of the join rows will be used to update the target row,
but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within
sub-selects is safer, though often harder to read and slower than using
a join. "

HTH,

Marc Mamin