13

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!

Improve this question
1
  • 1
    TOP x is not in the SQL standard, it's an extension unique to MS SQL Server (and probably Sybase). FETCH FIRST x ROWS was introduced in SQL:2008
    – bhamby
    Commented Jun 12, 2012 at 16:25

2 Answers 2

Reset to default
21

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?

Improve this answer
2
  • 1
    Unfortunately there is no unique identifier on the rows, which adds to the fun, but I have found a similar solution based on the example you provided, so thank you. 100 is not a hard limit, but rather a way to keep from grabbing the entire table if there are tens of thousands of records. Essentially, I have a scheduled process on a JEE server that runs across a number of instances that checks for records in this table, grabs them 100 at a time and processes them into another location. I want to mark the first 100 with a batch ID, select them, loop through and process, then delete by batch ID
    – Dave Shuck
    Commented Jun 12, 2012 at 16:42
  • 1
    I found that it can be done a little less verbosely: UPDATE (SELECT table1Key FROM table1 WHERE field1 <> 1 ORDER BY field1 FETCH FIRST 100 ROWS ONLY) SET field1 = 1
    – Isaac Betesh
    Commented Jan 22, 2018 at 14:39
2

Could you use the RRN (if you're just concerned with limiting the number of updates)?

for example:

update mytable a set a.field = 'foo' where RRN(a) < 200
Improve this answer
1
  • Not for a process like this. RRN() is okay for a non-volatile file, but this file is having rows inserted and deleted. By default, SQL tables will have REUSEDLT(*YES), so rows may be inserted in the previously deleted rows' slots. It'd be too easy to update the wrong rows with a batch number, or maybe worse delete a row that was inserted at RRN()=1.
    – user2338816
    Commented Apr 5, 2014 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.