CODEWITHSUNDEEP
mysqlsqlselectsql-update
Shahid Ghafoor
Shahid Ghafoor

Reputation: 3103

set variable value in update statement

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;

Upvotes: 0

Views: 1049

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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;

Upvotes: 1

Related Questions