Re: Update the first 1500 records that need updating

Since the amount of data to effect the request is limited to, for example, only 1500 rows, then simply create a temporary table for the purpose of the update.

Given the database is well formed, the RRN() can be avoided, and the primary key used to perform the update.

<code>

-- generate sample, against which to test update logic
create table qtemp.key_samp as
( select * from TheLib.TheFile
where <keyuno='literal'; some large sample size>
) with data

-- generate temp table with only 1500 rows of the
-- matching rows to be updated using join across
-- the four fields making up the primary key
create table qtemp.key_lmtd as
( with lmtdta as
( select keyuno,keydue,keytre,keyqua
from TheLib.TheFile i where i.updfld='X'
fetch first 1500 rows only
)
select * from lmtdta
) with data

-- perform the update, joining on unique key
-- temp table has no keys, but it is always /small/
update qtemp.key_samp u /* TheLib.TheFile post test */
set updfld='N'
where exists
(select 'Matched Key Found'
from qtemp.key_samp z /* TheLib.TheFile post test */
inner join qtemp.key_lmtd l
on (z.keyuno,z.keydue,z.keytre,z.keyqua) =
(l.keyuno,l.keydue,l.keytre,l.keyqua)
and (u.keyuno,u.keydue,u.keytre,u.keyqua) =
(z.keyuno,z.keydue,z.keytre,z.keyqua)
/* the above 'and' could be a WHERE */
)
and u.updfld='X' /* It may already have changed; this
may be important; e.g. a trigger */

</code>

Regards, Chuck