Menu
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!
277
1) Using Db2 FETCH clause to get the top-N rows The ORDER BY clause sorts books by ratings from high to low. The FETCH clause picks only the first 10 rows, which have the highest ratings.
Syntax: UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition; Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
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?
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
