Sponsored Links
Sponsored Link

sponsored links

Collapse

Announcement

Collapse
No announcement yet.

SQL Update all records, but in a specific order?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Update all records, but in a specific order?

    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:
    Code:
    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?

  • #2
    Originally posted by Vectorspace View Post
    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.

    Comment


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

      Code:
      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

      Comment


      • #4
        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.

        Code:
        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.

        Comment


        • #5
          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

          Comment

          sponsored links

          Collapse

          Working...
          X