[DB2-L] UPDATE & FETCH FIRST

Walter Janißen

[DB2-L] UPDATE & FETCH FIRST
Patricia

I think in DB2 V9 NFM it is possible:

UPDATE creatorid.table_name1
SET fields1 = value
WHERE 'key' IN (SELECT 'key'
FROM creatorid.table_name1
FETCH FIRST 10 ROWS ONLY);

Key should be the primary or unique key. If the key contains more than 1 column you can code it like this:

WHERE (key-column-1, ..., key-column-n) IN (SELECT key-column-1, .... key-column-n ...

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996




________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Patricia Anderson
Gesendet: Montag, 19. Januar 2009 20:11
An: [login to unmask email]
Betreff: [DB2-L] UPDATE & FETCH FIRST


Hello,

Is there a simple way to update, lets say only 5 records in a table ?

I was using the FETCH FIRST...ONLY clause:

UPDATE creatorid.table_name1
SET fields1 = value
WHERE (SELECT *
FROM creatorid.table_name1
FETCH FIRST 10 ROWS ONLY);

But it does't seem to work with the UPDATE statement.
The rows may be read only and can't be used.

Does anybody know of a work-around for it ?

I'm hoping not having to code a cobol program to do this.

Cheers,
Patricia


________________________________


IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

M. Khalid Khan

Re: UPDATE & FETCH FIRST
(in response to Walter Janißen)
It's not possible with V8 SQL alone. I once wrote an User Defined Function
(named SEQ) which returned the next integer starting with 1. With this
function I could update a subset of rows as follows :

UPDATE ...
SET ...
WHERE seq() <= 5

Off course this updates 5 rows of its own choosing and UDF was implemented
as a COBOL program. So there is no escape from coding a program.
Khalid






"Patricia Anderson" <[login to unmask email]>
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
01/19/2009 01:11 PM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] UPDATE & FETCH FIRST







Hello,

Is there a simple way to update, lets say only 5 records in a table ?

I was using the FETCH FIRST...ONLY clause:

UPDATE creatorid.table_name1
SET fields1 = value
WHERE (SELECT *
FROM creatorid.table_name1
FETCH FIRST 10 ROWS ONLY);

But it does't seem to work with the UPDATE statement.
The rows may be read only and can't be used.

Does anybody know of a work-around for it ?

I'm hoping not having to code a cobol program to do this.

Cheers,
Patricia



**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html