× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.