mysql - 在更新语句中设置变量值

我有以下存储过程

DELIMITER $$

USE `vanter`$$

DROP PROCEDURE IF EXISTS `updateItemQuantity`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateItemQuantity`(IN _quantity DOUBLE, IN _puCost DOUBLE, IN _itemState INT, IN _itemId LONG, IN _dateTime DATETIME)
BEGIN


SELECT @PUCOST := PU_COST FROM itemmanagement WHERE id=_itemId;

UPDATE itemmanagement  SET QUANTITY =QUANTITY+_quantity
WHERE id=_itemId;

INSERT INTO item_management_detail( DATE_TIME, ITEM_DETAIL_STATE, FK_ITEM, PU_COST, QUANTITY)
VALUES (_dateTime, _itemState, _itemId, @PUCOST, _quantity);
END$$

DELIMITER ;

我有以下两条语句在数据库中命中两次

SELECT @PUCOST := PU_COST FROM itemmanagement WHERE id=_itemId;

UPDATE itemmanagement  SET QUANTITY =QUANTITY+_quantity
WHERE id=_itemId;

我想在更新语句中将值分配给@PUCOST，如下所示，任何解决方案？

UPDATE itemmanagement  SET QUANTITY =QUANTITY+_quantity , @PUCOST := PU_COST
WHERE id=_itemId;

最佳答案

您可以使用case：

UPDATE itemmanagement
    SET QUANTITY = (CASE WHEN (@PUCOST := PU_COST) = NULL THEN NULL -- never get here
                         ELSE QUANTITY + _quantity
                    END)
WHERE id = _itemId;

关于mysql - 在更新语句中设置变量值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33026488/