Update first 100 rows..



IBM's flagship relational database management system

Update first 100 rows..

Postby mukesh.py » Mon Jul 06, 2009 3:28 pm

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...
mukesh.py
 
Posts: 8
Joined: Tue Nov 11, 2008 10:11 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Update first 100 rows..

 

Re: Update first 100 rows..

Postby dick scherrer » Mon Jul 06, 2009 11:59 pm

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Update first 100 rows..

Postby mukesh.py » Tue Jul 07, 2009 4:53 pm

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.
mukesh.py
 
Posts: 8
Joined: Tue Nov 11, 2008 10:11 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Update first 100 rows..

Postby dick scherrer » Wed Jul 08, 2009 2:26 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Update first 100 rows..

Postby mukesh.py » Wed Jul 08, 2009 4:09 pm

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...........................
mukesh.py
 
Posts: 8
Joined: Tue Nov 11, 2008 10:11 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post