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

Question

我有以下存储过程

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;

Answer 1

您可以使用案例:

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