Updates and rownums

Nihar Vaidya

Updates and rownums
Assume a table T with columns c1, c2 and c3, all integers. c1 is primary key
and is not null. Let's say it has 2 million rows in it. 1M of these rows
have c2=2. In Oracle, I can do the following:

update T set c2=5 where c2=2 and rownum < 10001;

This SQL changes updates only 10000 of those 1M rows where c2 was 2.

How do I do this in DB2 ?

Thanks.

- Nihar



Michael Bednarz

Re: Updates and rownums
(in response to Nihar Vaidya)
Hi Nihar,

is there any relation between rownum and col1?

Michael Bednarz

Computer Associates Germany, Düsseldorf
FSG Consultant
Phone +49 (0) 211 5306 295
Mobile +49 (0) 170 85 38 576
Fax: +49 (0) 211 5306 200

[login to unmask email] <mailto:[login to unmask email]>





-----Original Message-----
From: Nihar Vaidya [mailto:[login to unmask email]
Sent: Friday, December 13, 2002 12:31 AM
To: [login to unmask email]
Subject: Updates and rownums


Assume a table T with columns c1, c2 and c3, all integers. c1 is primary key
and is not null. Let's say it has 2 million rows in it. 1M of these rows
have c2=2. In Oracle, I can do the following:

update T set c2=5 where c2=2 and rownum < 10001;

This SQL changes updates only 10000 of those 1M rows where c2 was 2.

How do I do this in DB2 ?

Thanks.

- Nihar


visit the


Nihar Vaidya

Re: Updates and rownums
(in response to Michael Bednarz)
There is no relation. I set c1 as a primary key in my example, but it could
be anything, even a nullable column.

- Nihar

-----Original Message-----
From: Bednarz, Michael [mailto:[login to unmask email]
Sent: Friday, December 13, 2002 1:01 AM
To: [login to unmask email]
Subject: Re: Updates and rownums



Hi Nihar,

is there any relation between rownum and col1?

Michael Bednarz

Computer Associates Germany, Düsseldorf
FSG Consultant
Phone +49 (0) 211 5306 295
Mobile +49 (0) 170 85 38 576
Fax: +49 (0) 211 5306 200

[login to unmask email] < mailto:[login to unmask email]
<mailto:[login to unmask email]> >



-----Original Message-----
From: Nihar Vaidya [ mailto:[login to unmask email] <mailto:[login to unmask email]> ]
Sent: Friday, December 13, 2002 12:31 AM
To: [login to unmask email]
Subject: Updates and rownums


Assume a table T with columns c1, c2 and c3, all integers. c1 is primary key

and is not null. Let's say it has 2 million rows in it. 1M of these rows
have c2=2. In Oracle, I can do the following:

update T set c2=5 where c2=2 and rownum < 10001;

This SQL changes updates only 10000 of those 1M rows where c2 was 2.

How do I do this in DB2 ?

Thanks.

- Nihar


visit the
DB2-L webpage at http://listserv.ylassoc.com < http://listserv.ylassoc.com > .
The owners of the list can

Syed (TATA) Fazal

Re: Updates and rownums
(in response to Nihar Vaidya)
Nihar,

As far as I know with a plain SQL you cant do it and from what you have
mentioned it seems there is no other relationship between the column c2 to
any other column in the table. Maybe someone can chip in better answer.

Regards,
Syed




-----Original Message-----
From: Nihar Vaidya [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 6:31 PM
To: [login to unmask email]
Subject: Updates and rownums


Assume a table T with columns c1, c2 and c3, all integers. c1 is primary key
and is not null. Let's say it has 2 million rows in it. 1M of these rows
have c2=2. In Oracle, I can do the following:

update T set c2=5 where c2=2 and rownum < 10001;

This SQL changes updates only 10000 of those 1M rows where c2 was 2.

How do I do this in DB2 ?

Thanks.

- Nihar








Terry Purcell

Re: Updates and rownums
(in response to Syed (TATA) Fazal)
Nihar,

If you are using DB2 on UNIX/Windows/Linux, then you can use the ROW_NUMBER
function in a subquery such as:

UPDATE T
SET C2 = 5
WHERE C2 = 2
AND C1 <
(SELECT MAX(C1)
FROM
(SELECT C1, ROW_NUMBER () OVER (ORDER BY C1) AS ROWNUM
FROM T
WHERE C2 = 2)
WHERE ROW_NUM < 1001)

Note: This expects a primary key such as C1.

On the mainframe there is no elegant solution. There may even be a simpler
way to do this than above, but I have not explored this any further.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Fazal, Syed (TATA)
Sent: Friday, December 13, 2002 11:12 AM
To: [login to unmask email]
Subject: Re: Updates and rownums


Nihar,

As far as I know with a plain SQL you cant do it and from what you have
mentioned it seems there is no other relationship between the column c2 to
any other column in the table. Maybe someone can chip in better answer.

Regards,
Syed




-----Original Message-----
From: Nihar Vaidya [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 6:31 PM
To: [login to unmask email]
Subject: Updates and rownums


Assume a table T with columns c1, c2 and c3, all integers. c1 is primary key
and is not null. Let's say it has 2 million rows in it. 1M of these rows
have c2=2. In Oracle, I can do the following:

update T set c2=5 where c2=2 and rownum < 10001;

This SQL changes updates only 10000 of those 1M rows where c2 was 2.

How do I do this in DB2 ?

Thanks.

- Nihar