Hi,

I'm using DB2 Enterprise Edition 7.2 fixpack 3 on Windows 2000.

I have a table that can potentially have millions of rows, and I need to incrementally update the table contents.

In Oracle I acheive this using the rownum keyword, e.g:

update table
set status=1, col=<something>
where status=0
and rownum <= 5000

How can I do this in DB2?


Thanks,

Lior

I think you can!

However, since DB2 has no rownum feature, you must do it somewhat complicated way:

update table
set status = 1, col=<something>
where status=0
and unique_key in (
select unique_key
where status=0
fetch first 5000 rows );
-- [caveat emptor, I have not tried this]

If you don't have a unique_key, then the best idea is to make a program, where you can control how many updated rows you want to have.

Cheers, Bill

I guess hurmavi's solution will work in V8 but not in v7 ..

In V7, the following will work ...
update table set status=1,col=<something>
where status=0 and
col1 in (select col1 from (select col1,rownumber() over () as row# from table) as t1 where row#<5000)

hth
Sathyaram

Hi,

I tried using a unique key in this way, and it doesn't work. DB2 balks at the "fetch first 50 rows only" stanza in the sub-query.

echo create the test table and fill it with some test data
create table test (pk bigint not null, status bigint not null, data bigint not null);
alter table test add primary key (pk);
insert into test select row_number() over(), 0, 0 from sysibm.systables;

echo set the data to the pk value
update test set data=pk;

This statement works:

update test
set status=1, data=0
where pk in (select pk
from test
where status=0)

This statement doesn't work:

update test
set status=1, data=0
where pk in (select pk
from test
where status=0
fetch first 5 rows only)

I get the following:
SQL0104N An unexpected token "update test set status=1, data=0 where pk " was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601


I tested the sub-query on its own and it works.
select pk
from test
where status=0
fetch first 5 rows only

I was able to do this:
update test set status=1, data=0
where test.pk in (select test.pk
from test
inner join (select row_number() over () as RN, pk
from test ) b on test.pk=b.pk and b.RN <= 5)

The question is, how bad is this on performance?

Thanks,
Lior