RE: Update a single record using SQL

If you need sequence number to reset with each "group", you could do this
(where KEY1,KEY2,SEQNBR is unique and KEY1,KEY2 defines the group).


CREATE TABLE QTEMP/TEMPTABLE AS (
SELECT KEY1, KEY2, SEQNBR
FROM MYTABLE
ORDER BY KEY1, KEY2, SEQNBR
) WITH DATA
Table TEMPTABLE created in QTEMP.

SELECT * FROM QTEMP/TEMPTABLE

KEY1 KEY2 SEQNBR
AA AA 1
AA AA 2
AA BB 3
BB BB 4


UPDATE MYTABLE A SET SEQNBR = (
SELECT 5*(RRN(B) + 1 - (
SELECT MIN(RRN(C))
FROM TEMPTABLE C
WHERE A.KEY1=C.KEY1 AND A.KEY2=C.KEY2)
)
FROM TEMPTABLE B
WHERE A.KEY1=B.KEY1 AND A.KEY2 = B.KEY2 AND A.SEQNBR=B.SEQNBR
)
4 rows updated in MYTABLE in QTEMP.

SELECT KEY1, KEY2, SEQNBR FROM MYTABLE

KEY1 KEY2 SEQNBR
AA AA 5
AA AA 10
AA BB 5
BB BB 5



Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Fear less, hope more,
eat less, chew more,
whine less, breathe more,
talk less, say more,
hate less, love more,
and all good things will be yours.
-- Swedish proverb

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Thursday, June 17, 2010 4:43 AM
To: 'RPG programming on the IBM i / System i'; 'Midrange Systems
Technical Discussion'
Subject: RE: Update a single record using SQL

This doesn't sound like an RPG question, so I've cross-posted my
response to
midrange-l. Responses should probably go there.

To be effective with this, we would need to know more about your data,
and
your OS version et cetera. Maybe describe the table involved, what
you're
trying to achieve, and what you've tried.

Having said that, if your release level SQL includes RRN, you should be
able
to do something like (untested), which will work only if SEQNBR is
unique:

CREATE TABLE QTEMP/TEMPTABLE AS (SELECT SEQNBR FROM MYTABLE ORDER BY
SEQNBR)
WITH DATA ;
UPDATE MYTABLE A SET SEQNBR = (select rrn(TEMPTABLE) * 10 FROM
QTEMP/tempTABLE B WHERE A.SEQNBR = B.SEQNBR) ;

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"We must believe in luck. For how else can we explain the success of
those
we don't like?"

I am trying to update a series of records in a file using SQL. The
records
are in groups that are the same except for a sequence number field.

If

try
to do an Update it puts the same number in all the records. What i

want

to
do is re-sequence the sequence number. I would appreciate any help I
can
get. Thanks