如果第一次更新成功执行，则执行第二次更新 [英] Execute second update if first update execute successfully

问题描述

存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sample`(IN itemId INT, IN itemQnty 
DOUBLE, IN invID INT)

BEGIN

DECLARE crntQnty DOUBLE;
DECLARE nwQnty DOUBLE;

SET crntQnty=(SELECT `QuantityOnHand` FROM `item` WHERE id=itemId);
SET nwQnty=itemQnty+crntQnty;
UPDATE `item` SET `QuantityOnHand`=nwQnty WHERE `Id`=itemId;

UPDATE `inventoryentry` SET `Status` = 1 WHERE `InventoryID`=invID AND 
`ItemID`=itemId;


END$$

在此存储过程中，我有两个更新语句.如果第一次更新成功执行，然后第二次执行.需要进行哪些更改?

In this stored procedure I have two update statements. IF first update execute successfully then second execute. what changes required to be made?

推荐答案

在第一个UPDATE之后，您可以检查受ROW_COUNT()影响的行数.

After the first UPDATE you can check the number of rows that were affected by ROW_COUNT().

如果ROW_COUNT()返回所需的更新数量(也许> 0)，则仅触发第二个UPDATE.

If the ROW_COUNT() returns the desired number of updates you want (maybe > 0) then only fire second UPDATE.

Yo可以用IF - END IF包围第二个UPDATE，检查受影响的行数.

Yo can surround the second UPDATE by an IF - END IF checking for the number of rows that were affected.

这篇关于如果第一次更新成功执行，则执行第二次更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！