How to lock rows for update and then update a table using a variable in MySQL

Question

I have a purchasing table that stores all orders that a store receives. In some cases, I have to re-order the an item at a different cost. When I make a sale transaction, I need to be able to determine the total cost of the item sold using FIFO (First In, First Our) system determined by the purchased date.

Here is how my table look like

CREATE TABLE `purchases` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(11) unsigned NOT NULL,
  `purchased_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cost` decimal(10,2) unsigned NOT NULL,
  `availableQty` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `purchases` (`id`, `item_id`, `purchased_on`, `cost`, `availableQty`) VALUES
(1, 1, '2015-01-13 23:50:09', '1.00', 10),
(2, 1, '2015-01-13 23:50:10', '0.90', 20),
(3, 1, '2015-01-13 23:50:11', '0.80', 50),
(4, 2, '2015-01-13 23:50:11', '10.80', 18),
(5, 2, '2015-01-13 23:50:11', '20.25', 235);

Using the data above, if some one want to purchase 40 units of item_id = 1, then the cost is calculated like so (10 X 1.00) + (20 X 0.90) + (10 X 0.80) = 36 . The total cost for this transaction for item_id = 1 is $36.

But now, I need to update the availableQty column with the new available values.

Based on the values in the availableQty column, row 1 and 2 should have a value of 0 in the availableQty column and 40 left for row 3 of the same column.

To calculate the cost of the transaction, I was able to write this query that will do the cost calculation.

SET @orgMyQty = 40;
SET @myQty = @orgMyQty;
SELECT SUM(cost) AS total_cost, SUM(availableQty) AS totalAvailable, SUM(availableQty) - @orgMyQty AS RemainingUnites
FROM (
    SELECT 
    CASE WHEN availableQty >= @myQty THEN cost * @myQty
         ELSE cost * availableQty END AS cost,
    CASE WHEN @myQty > availableQty THEN @myQty := @myQty - availableQty END AS left_over,
    availableQty
    FROM purchases
    WHERE item_id = 1 AND @myQty > 0
    ORDER BY purchased_on ASC, item_id ASC
) AS t

But How Can I update each row with the new totalAvailable Value?

so the final records will need to look like this

(1, 1, '2015-01-13 23:50:09', '1.00', 0),
(2, 1, '2015-01-13 23:50:10', '0.90', 0),
(3, 1, '2015-01-13 23:50:11', '0.80', 40),
(4, 2, '2015-01-13 23:50:11', '10.80', 18),
(5, 2, '2015-01-13 23:50:11', '20.25', 235);

So my questions is this

1. How to execute the UPDATE correctly?
2. How to use the query query to lock the rows for update so no other transaction can change the value until this transaction is committed. Note, both queries select and update are in the same transaction.
3. Is there a better approach to calculate the cost?

I tried the following query to update the values

-- This is giving me a syntax error on this line @myQty := CASE WHEN @myQty > availableQty THEN @myQty - availableQty ELSE @myQty END

SET @myQty = 40;
UPDATE data_import.purchases
SET qty = CASE WHEN @myQty = 0 THEN 0
               WHEN @myQty > 0 AND availableQty >= @myQty THEN cost * @myQty
               ELSE cost * availableQty END,
@myQty := CASE WHEN @myQty > availableQty THEN @myQty - availableQty  ELSE @myQty END
WHERE item_id = 1
ORDER BY purchased_on ASC, item_id ASC;

I also tried this query

-- there is not syntax error here but only the last column was updated incorrectly.
    UPDATE data_import.purchases
    SET qty = CASE WHEN @myQty = 0 THEN 0
                   WHEN @myQty > 0 AND availableQty >= @myQty THEN cost * @myQty
                   ELSE cost * availableQty END
    WHERE item_id = 1 AND @myQty := CASE WHEN @myQty > availableQty THEN @myQty - availableQty  ELSE @myQty END
    ORDER BY purchased_on ASC;

Answer 1

here is a little bit different solution to calculate cost:

select sum(cost *
least(
  availableQty,
    greatest(
      0,
      40-ifnull(
        (select sum(availableQty) from purchases as b where b.id<a.id),
        0
      )
    )
  ))
as totalCost
from purchases as a
where item_id = 1

some notes how it works:

Return how many we still should buy for current row:

greatest(
    0,
    40 - ifnull((select sum(availableQty) from purchases as b where b.id<a.id),0)
)

For each row we calculate how many we bought from previous rows, ie we summarize available quantity for all previous rows, subtract this number from 40 (amount we want to buy) and we have rest.

This is basically the same loop you already have, but you're decreasing "rest" every iteration, I recalculate whole previous sum and find difference between it and total demand, so I get rest for each row without loop.

But we can have negative number, this is why we use greatest. So for positive values (ie previous rows didn't fulfill demand) we get how many we still need to select from other rows, for all others - 0, ie all demand is satisfied.

How many we can "buy" from current row:

least(
      availableQty,
      demandRest /* construction above */
      )

We just select either whole available quantity or unfulfilled demand, depending which is smaller

So, basically for each row we either get whole available quantity, or demand rest or 0 if demand was fulfilled by previous rows

here is solution for update:

update purchases as u
inner join
(
select id,
least(
  availableQty,
    greatest(
      0,
      40-ifnull(
        (select sum(availableQty) from purchases as b where b.id<a.id),
        0
      )
    )
  )
as usedQty
from purchases as a
where item_id = 1) as t
  on u.id = t.id
  set u.availableQty = u.availableQty-t.usedQty;

fiddle: http://sqlfiddle.com/#!9/0a756/1