Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

UPDATE with primary key fails in stored procedure

netgertnetgert Member Posts: 331
Hello

In my stored procedure when I do the following:
[code]UPDATE `categories` SET `left` = `newleft`, `right` = `newleft` WHERE `uuid` = `child_uuid`;[/code]
Then the row will not be updated. The following code results in infinite loop:
[code]REPEAT
UPDATE `categories` SET `left` = `newleft`, `right` = `newleft` WHERE `uuid` = `child_uuid`;
SELECT `left`, `right` INTO `temp1`, `temp2` FROM `categories` WHERE `uuid` = `child_uuid`;
UNTIL `temp1` = `newleft` AND `temp2` = `newleft` END REPEAT;[/code]

However, when I change the operator from = to != in the UPDATE statements, all rows except the one with `child_uuid` as `uuid` will get updated. Its just that when I explicitly select the value for primary key that I know exists, 0 rows get updated.

Is this a bug? Any workarounds?
[hr][red][italic][b]N[/b][/red][blue]et[/blue][red][b]G[/b][/red][blue]ert[/italic][/blue][hr]

Comments

  • netgertnetgert Member Posts: 331
    : Hello
    :
    : In my stored procedure when I do the following:
    : [code]: UPDATE `categories` SET `left` = `newleft`, `right` = `newleft` WHERE `uuid` = `child_uuid`;[/code]:
    : Then the row will not be updated. The following code results in
    : infinite loop:
    : [code]: REPEAT
    : UPDATE `categories` SET `left` = `newleft`, `right` = `newleft` WHERE `uuid` = `child_uuid`;
    : SELECT `left`, `right` INTO `temp1`, `temp2` FROM `categories` WHERE `uuid` = `child_uuid`;
    : UNTIL `temp1` = `newleft` AND `temp2` = `newleft` END REPEAT;[/code]:
    :
    : However, when I change the operator from = to != in the UPDATE
    : statements, all rows except the one with `child_uuid` as `uuid` will
    : get updated. Its just that when I explicitly select the value for
    : primary key that I know exists, 0 rows get updated.
    :
    : Is this a bug? Any workarounds?

    A simpler example:
    [code] UPDATE `categories` SET `parent` = `newparent` WHERE `uuid` = `olduuid`;
    SELECT *, `newparent` FROM `categories` WHERE `uuid` = `olduuid`;[/code]
    This returns:
    [code]'00000000-0000-0000-0000-000000000001', NULL, 5, 5, 5, 'Test', '00000000-0000-0000-0000-000000000002'[/code]
    G
Sign In or Register to comment.