更新库存数量

Question

我有问题，以更新我的库存数量从库存表基于大小从购物车表

股票

| size | quantity |
| -----| ---------|
| XS   | 100      |
| S    | 100      |
| M    | 100      |
| L    | 100      |
| XL   | 100      |
| 2XL  | 100      |
| 3XL  | 100      |

购物车

| size | quantity |
| -----| ---------|
| XS   | 3        |
| 2XL  | 5        |

在我运行我的代码之后，stock表将如下所示

股票

| size | quantity |
| -----| ---------|
| XS   | 97       |
| S    | 0        |
| M    | 0        |
| L    | 0        |
| XL   | 0        |
| 2XL  | 95       |
| 3XL  | 0        |

应该是这样的

股票

| size | quantity |
| -----| ---------|
| XS   | 97       |
| S    | 100      |
| M    | 100      |
| L    | 100      |
| XL   | 100      |
| 2XL  | 95       |
| 3XL  | 100      |

我的代码

include "db_connect.php";
//update stock quantity
  $stockquery = mysqli_query($connect, "UPDATE stock SET Quantity = Quantity - (SELECT SUM(quantity) FROM cart WHERE size = stock.Size)");

  if(mysqli_query($connect, $stockquery))  
  {  
      echo ' stock Successful';  
  }
  else
  {
      echo ' stock Failed';
  }

输出结果也是库存失败

Answer 1

在多表更新中，您需要：

UPDATE stock 
JOIN ( SELECT size, SUM(quantity) quantity
       FROM cart
       GROUP BY size ) total USING (size)
SET stock.quantity = stock.quantity - total.quantity