找到你要的答案

Q:set variable value in update statement

Q:在更新语句中设置变量值

I have the following stored procedure

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 ;

I have two following statements that hit two time in db

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

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

I want to assign the value to @PUCOST in update statement, as follow , any resolution?

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

我有以下存储过程

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 ;

我有两个下面的语句,击中了两次在DB

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

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

我要分配的价值”pucost在UPDATE语句,如下,任何决议?

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

You could use a case:

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

你可以使用一个案例:

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