SQL statement MySQL

Question

UPDATE product, 
       claimdetail 
   SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) 
 WHERE product.ProductId =  claimdetail.productcode

The code above is to deduct product from stock when staff is use product to claim it have a problem. If the staff use the same product 2 time it deduct only one time, how can I fix it?

Such as claimdetail have the product code "4712893150132" in many record it deduct only one record it should deduct all records.

Answer 1

UPDATE product
JOIN
(
SELECT productcode, SUM(quantity) SUMClaim
FROM claimdetail
GROUP BY productcode
) claims on claims.productcode = product.ProductId
SET product.ProductQuantity = product.ProductQuantity - claims.SUMClaim

Answer 2

UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE product.ProductId = claimdetail.productcode