ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Issue doing non-cursor update in a cursor loop

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

  • Issue doing non-cursor update in a cursor loop

    I have to update pairs of matching records in a single table. The records in each pair are compared with each other, and then both are updated based on the results of that comparison. Sometimes only one or the other may exist.

    So, I have an SQL to join the pairs together and read through each pair, accounting for absences of one or the other.
    Because it's a join I cannot update through the cursor, so I am doing an independent update using the key values returned by the cursor to identify the record.

    But this is messing up the cursor read. Updating a record seemed to change the record's position in the read order (I was not specifying an order by), and caused the next fetch to return the same pair of records, several times over.

    Adding an Order By for the unique key seems to have resolved it, but I would still like to understand why this is happening. I was not updating the key fields, but I guess maybe since I was not specifying a sort order, the SQL engine was picking its own and just happened to be using one of the non-key fields as the sort? Any advice how I can avoid this in the future, or a better approach?

    The effect I am going for is as follows. I have this table:
    Code:
    ID1    ID2    Code    CodeValue
    ---    ---    ----    ---------
    AAA    111    A001    something
    AAA    111    A002    someOtherThing
    AAA    111    A003    someThirdThing
    AAA    222    A001    something
    AAA    222    A002    someOtherThing
    AAA    222    A003    someThirdThing
    AAA    547    A001    something
    AAA    547    A003    someThirdThing
    BBB    987    A001    something
    ID1, ID2, and Code make up the unique key.

    For a given value of ID1, for each value of ID2, I need to compare the CodeValues for Code A001 and A002, and then update both CodeValues depending on the results of that comparison. Some combinations of ID1/ID2 may not have both.

    E.g. I am looking at ID1 = AAA.
    I would compare CodeValue for AAA 111 A001 and AAA 111 A002, and utpate both those CodeValues accordingly.
    Then I would look at AAA 222 A001/A002 and repeat.

  • #2
    Have you considered using an updateable sql view?

    Sometimes using SQL to perform simultaneous inserts, updates and deletes against related tables can be a chore. It would be convenient if programmers could simply treat related tables as a single table for the purpose of modifying data. For simplicity, consider the following common scenario: An iSeries has multiple item tables holding data related in


    Walt

    Comment


    • #3
      Do you really need a cursor? Maybe an update statement can update all the records in one step. I usually create a select statement showing the CodeValue and 'New Code Value' - if I can get that correct then it is simple to convert to an update statement.

      Comment


      • #4
        An updateable view would work, but that requires triggers which I would like to avoid.

        I do need a cursor, the comparison and update is too complex to implement entirely within SQL. It involves callouts to other procedures.

        Comment

        Working...
        X