Re: Update Statement and frequent commits

[Michel Esber]

Ok .. I know I sent a bad example. Here´s another try:

update T set field = 'new_value' where (ID) in (select ID from T where
field1='xyz' and field2 = 'foo' fetch first 1000 rows only)

However , the plan has two index scans. Is there any better solution
with a single scan ?

The delete statement posted before has only one index scan ... Can I
achieve the same with update ?

Thanks, Michel

[Serge Rielau]

Re: Update Statement and frequent commits

Michel Esber wrote:

Ok .. I know I sent a bad example. Here´s another try:
>
update T set field = 'new_value' where (ID) in (select ID from T where
field1='xyz' and field2 = 'foo' fetch first 1000 rows only)
>
However , the plan has two index scans. Is there any better solution
with a single scan ?
>
The delete statement posted before has only one index scan ... Can I
achieve the same with update ?

Yes. The trick is to use the select as the update target, just as the
delete is.
UPDATE (SELECT ....) AS T SET ...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab