RE: Update the first 1500 records that need updating

I agree that the FETCH FIRST won't work on the update.

However if it did, then it would work. FETCH FIRST limits the number of rows returned that meet the
WHERE clause, not the total number of rows read by the DB.

Also, I agree that I don't understand why you'd want to do this <grin>

Alan,

The only way I see to do it is to build a temporary file with the unique keys of the records you want
to update. I as thinking you could maybe make use of a CTE, but I can't seem to get the syntax to
work.

May want to just use record level access to this, either as an SQL stored procedure using a cursor, an
RPG program with a cursor, or RPG native I/O.


Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Tuesday, December 04, 2007 4:47 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Update the first 1500 records that need updating

I don't think this will work. First, I'm pretty sure that
you can't have a
FETCH FIRST clause on an UPDATE statement. Second, if I read
Alan's request
correctly, he doesn't want to update the first 1500 records,
he wants to
update the first 1500 records which match the condition
(FIELDA = 'X').

For example, let's say half the records have FIELDA = 'X'.
Your suggestion
would update (approximately) 750 records, since you are
processing 1500 of
which only half meet the criteria. In this specific case, Alan would
actually need to process 3000 records in order to update 1500 records.

After looking at the situation carefully, I have to say I
can't see any easy
way to do it. Then again, I don't understand the "WHY"
unless the issue is
simply to reduce the processing time.

Joe

From: Francis Lapeyre

Add FETCH FIRST 1500 ROWS ONLY to the statement.

On Dec 4, 2007 3:23 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi everyone
(Hopefully) A quick sql question.
I have a simple SQL update
UPDATE FILEA SET FIELDA = 'N' WHERE FIELDA = 'X'

However there are thousands of records that will be

updated. I only need

to update the first 1500 of these records (no other

selection criteria,

just the first 1500)

Is this possible?
If so, how?

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.