How can I update top 100 rows in DB2
Could you use the
RRN (if you're just concerned with limiting the number of updates)?
update mytable a set a.field = 'foo' where RRN(a) < 200
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!
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.
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'