Page 1 of 1

Update first 100 rows..

PostPosted: Mon Jul 06, 2009 3:28 pm
by mukesh.py
Hi,

How to update first 100 rows in a db2 by single update query.

example

key value
1 w
1 r
1 t
1 y
1 u
1 k
. .
. .
. .
150 times 150 times


Need to update first 100 rows in the value field as value = 'j' .

hope use of key field in the query is clear and need not need any explanation...

Re: Update first 100 rows..

PostPosted: Mon Jul 06, 2009 11:59 pm
by dick scherrer
Hello,

hope use of key field in the query is clear and need not need any explanation...
No, it is not. . .

What does the key field have to do with the requirement? Why did you even mention the key if it is not part of "the rules"?

There are no "first 100 rows" in a db2 (or any other relational) database table.

How have others with the same assignment addressed the requirement?

Re: Update first 100 rows..

PostPosted: Tue Jul 07, 2009 4:53 pm
by mukesh.py
update sample
set column2 = 'A'
where column1 = 123;

suppose there are 100 column meeting this criteria for update and the updation is required only on first 50 columns fetched for update. then how the above query will be modified.

Re: Update first 100 rows..

PostPosted: Wed Jul 08, 2009 2:26 am
by dick scherrer
Hello,

There is also no first 50 columns. . . :)

What business requirement is satisfied by updating some column in 50 unspecified/unpredictable rows?

For forcing an update of only 50 rows you could define a cursor, maintain a counter, and stop processing when 50 updates have been done.

Re: Update first 100 rows..

PostPosted: Wed Jul 08, 2009 4:09 pm
by mukesh.py
using counters and implementing it in a cobol pgm was not my requirement. i was just asking whether this can be achieved in a query through QMF. Please restrict it to a query and let me know if there is any way.
I have achieved it using the update time stamp. but if there is any way we can use in query to update as we use for select as 'fetch first 100 rows' etc...........................