Folks:

I am trying to find out what is the best way to restrict the number of
rows deleted in a single statement (so as to not blow out the logs). I
can repeatedly run the delete until there are no more rows to be
deleted.

I have implemented a solution that selects the Primary Key columns
along with row_number() over() in a temp table and then deletes based
on the row_number range. I was wondering if there is an
alternative/better way of doing this...

Thanks in advance.

Regards,
Nikhil

Blair Adamache <badamache@2muchspam.yahoo.com> writes:

> You don't have to use row_number in v8 - this syntax will work:
>
> delete from address where name in
> (select name from address
> order by name
> fetch first 1 row only)

Cool.

Do we support the new goodies on Common Table Expressions too ?

--
#include <disclaimer.std> /* I don't speak for IBM ... */
/* Heck, I don't even speak for myself */
/* Don't believe me ? Ask my wife :-) */
Richard D. Latham lathamr@us.ibm.com

I don't know.

You're probably best to install v8 Fixpak 3 and experiment (unless Serge
steps in with some advice).

Richard D. Latham wrote:

> Blair Adamache <badamache@2muchspam.yahoo.com> writes:
>
>
>>You don't have to use row_number in v8 - this syntax will work:
>>
>>delete from address where name in
>> (select name from address
>> order by name
>> fetch first 1 row only)
>
>
> Cool.
>
> Do we support the new goodies on Common Table Expressions too ?
>

Yes, no restrictions. This is V8 GA function btw.
Here is DB2 FP<soon>
DELETE FROM
(SELECT name FROM address ORDER BY name FETCH FIRST 1 ROW ONLY);

Come to my talk at DBM Tech in Vegas for more info :-)

Cheers
Serge