New to Mysql;
I have created a stored procedure. Using a cursor I traverse the first table.
For each row in the cursor I need to determine the correct row in another table to update.
Using a select statement with data from the cursor I identify the primary key to this the target table and update it. I extract row counts from the target table as I go to check that the correct number of rows are being updated (i.e. 1 each time). results here are inconsistent.
I then select the next row from the cursor and continue to the end. immediately before the end I commit and check the number of updated rows.
After the SP completes (i.e. on the next line, no other SQL statements.I do another rowcount on the target table.
However, this updated row count differs completely from the row count obtained from inside the SP.
Effectively all rows are updated on the target table and not just the intended ones. This is not indicated on the debug selects inside the SP.
Also as the cursor progresses through the routine, I get occasional spurious additional rows updated and returned from debug selects inside the cursor.
Any pointers as to where I should be looking to resolve this?
I have created a stored procedure. Using a cursor I traverse the first table.
For each row in the cursor I need to determine the correct row in another table to update.
Using a select statement with data from the cursor I identify the primary key to this the target table and update it. I extract row counts from the target table as I go to check that the correct number of rows are being updated (i.e. 1 each time). results here are inconsistent.
I then select the next row from the cursor and continue to the end. immediately before the end I commit and check the number of updated rows.
After the SP completes (i.e. on the next line, no other SQL statements.I do another rowcount on the target table.
However, this updated row count differs completely from the row count obtained from inside the SP.
Effectively all rows are updated on the target table and not just the intended ones. This is not indicated on the debug selects inside the SP.
Also as the cursor progresses through the routine, I get occasional spurious additional rows updated and returned from debug selects inside the cursor.
Any pointers as to where I should be looking to resolve this?
Comment