set variable value in update statement

Question

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;

currently i got this solution for now UPDATE itemmanagement SET QUANTITY =QUANTITY+_quantity, PU_COST=(@PUCOST := PU_COST) WHERE id=_itemId; But do not want to update PU_COST. lemme do more R&D — Shahid Ghafoor
– Shahid Ghafoor, Commented Oct 8, 2015 at 22:13

Gordon Linoff · Accepted Answer · 2015-10-09 03:06:31Z

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;

answered Oct 9, 2015 at 3:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shahid Ghafoor Over a year ago

This is not what I need

Gordon Linoff Over a year ago

@ShahidGhafoor . . . This appears to exactly answer your question.

Collectives™ on Stack Overflow

set variable value in update statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related