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