How can I update top 100 rows in DB2

Question

I know that in standard SQL you can do this:

update top (100) table1 set field1 = 1

(reference: how can I Update top 100 records in sql server)

But this is not allowed in DB2. Can anyone advise me on how to accomplish the same result in DB2? Thanks!

Answer 1

This is dooable, although you may not get the results you expect...

First, always remember that SQL is inherently UNORDERED. This means that there is no such thing as the 'top' rows, unless you explicitly define what you mean. Otherwise, your results are 'random' (sortof).

Regardless, this is dooable, presuming you have some sort of unique key on the table:

UPDATE table1 SET field1 = 1
WHERE table1Key IN (SELECT table1Key
                    FROM table1
                    WHERE field1 <> 1
                    ORDER BY field1
                    FETCH FIRST 100 ROWS ONLY)

Why do you only want to update 100 rows at a time? What sort of problem are you really trying to solve?