Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Update first n rows

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2004
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •