How can I update top 100 rows in DB2


Answers

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
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!




Without an ORDER BY the whole idea of TOP doesn't make much sense. You need to have a consistent definition of which direction is "up" and which is "down" for the concept of top to be meaningful.

Nonetheless SQL Server allows it but doesn't guarantee a deterministic result.

The UPDATE TOP syntax in the accepted answer does not support an ORDER BY clause but it is possible to get deterministic semantics here by using a CTE or derived table to define the desired sort order as below.

;WITH CTE AS 
( 
SELECT TOP 100 * 
FROM T1 
ORDER BY F2 
) 
UPDATE CTE SET F1='foo'



Links



Tags