SQL Update all records, but in a specific order?

[Vectorspace]

I have a file with unique primary key:
Ref - char(8)
Sequence - numeric(5,0)

An error meant that for ref ABCD, all the sequence numbers were 1 too low - 0 to 5 instead of 1 to 6, for instance.

The obvious solution is to update all the records to increment the sequence number:

update myfile
set Sequence = Sequence + 1
where Ref = 'ABCD'

But that fails with a duplicate record error. Say the file has records with keys:
ABCD 00000
ABCD 00001
ABCD 00002
ABCD 00003
ABCD 00004
ABCD 00005

Update All ABCDs will start with ABCD 00000, and try and update it to ABCD 00001, which would fail because ABCD 00001 already exists.
Although the end state after all the updates would be valid, because it updates 1 record at a time in ascending order the intermediary states after each individual update will be invalid.

In the end because the largest sequence was less than 100, I did two updates: one to add 100 and then another to subtract 99, to achieve the same final result.

But is there a better way? To make it do the updates in descending order instead, for instance?

[TedHolt]

In the end because the largest sequence was less than 100, I did two updates: one to add 100 and then another to subtract 99, to achieve the same final result. But is there a better way?

Not to my knowledge, Vector. That's the way I do such updates. If there is a better way, I hope someone will jump in and show us how.

It would be nice if we could use ORDER BY on an UPDATE statement.

[Jim_IT]

Using the MERGE statement and commitment control, you can accomplish updating the sequence numbers without causing a duplicate key error.

create table mylib.code400ex
(REF Char(8),
SEQUENCE Numeric(5,0));

create unique index mylib.code400exidx on mylib.code400ex (REF,SEQUENCE);

insert into mylib.code400ex
with nbrs ( n ) as (
   select 0 from (values 0) as a union all
   select 1 + n from nbrs where n < 9999 )
select 'abcd', n from nbrs;

call qsys2.qcmdexc('STRJRNPF FILE(MYLIB/CODE400EX) JRN(MYLIB/MYJRN) IMAGES(*BOTH) OMTJRNE');

merge into mylib.code400ex as old
using (select ref, sequence+1 from mylib.code400ex order by ref, sequence desc) as new (ref,sequence)
on (new.ref = old.ref and new.sequence-1 = old.sequence)
when matched then
update set old.sequence = new.sequence
atomic
with rr;

commit;

Jim

[Jim_IT]

There is this note as well in the DB2 for i Reference for MERGE:

A maximum of 500 000 000 rows can be acquired in any single MERGE statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) was specified.

You can also review the journal changes once complete.

Select
   ENTRY_TIMESTAMP, RECEIVER_NAME,
   RECEIVER_LIBRARY, JOURNAL_CODE, JOURNAL_ENTRY_TYPE, PROGRAM_NAME,
   SUBSTR(OBJECT, 1, 10) AS FILE_NAME, SUBSTR(OBJECT, 11, 10) AS
   FILE_LIB, JOB_NUMBER, JOB_USER, JOB_NAME,
   CAST(SUBSTR(ENTRY_DATA, 1, 13) AS CHAR(13)) AS TABLE_VALUES
From
TABLE(DISPLAY_JOURNAL('MYLIB', 'MYJRN',
                      '', '*CURRENT',
                      CAST(NULL AS TIMESTAMP),
                      CAST(NULL AS DECIMAL(21, 0)),
                      'R', '', 'MYLIB', 'CODE400EX',
                      '*FILE', '*ALL', '', '', '')) AS A

p.s. What happened to the ability to edit your posts? I left out the (*OPNCLO) parameter options for OMTJRNE parameter on my previous post.

[Vectorspace]

Thanks Ted for this new ITJungle article of yours: https://www.itjungle.com/2018/01/08/.../#comment-1810
UPDATE and DELETE now supports Order By