MySQL更新一张表中的多行

Question

+-----------+---------------+-------------+---------------------+
| ProductID | Description   | CostPerItem | NumberOfItemsOnHand |
+-----------+---------------+-------------+---------------------+
| 11        | Bike          |         150 |                 200 |
| 12        | Socks         |           2 |                 100 |
| 55        | Bicycle       |         150 |                 120 |
| 66        | Elbow pads    |          14 |                 120 |
| 78        | Knee Pads     |          12 |                  70 |
| 88        | Roller Blades |          75 |                  89 |
| 99        | Helmet        |          29 |                  30 |
+-----------+---------------+-------------+---------------------+

+------------+-----------+------------+-------------+-------------+---------+
| CustomerID | FirstName | LastName   | City        | PhoneNumber | AgentID |
+------------+-----------+------------+-------------+-------------+---------+
| 10         | Barney    | Rubble     | Bedrock     |        -457 | NULL    |
| 12         | Monty     | Burns      | Springfield |         789 | NULL    |
| 13         | Wonder    | Woman      | Kypto       |        -346 | NULL    |
| 14         | Peter     | Griffens   | Providence  |       -3580 | NULL    |
| 15         | Fred      | Flintstone | Bedrock     |       -1568 | NULL    |
| 21         | Homer     | Simpson    | Springfield |       -8270 | NULL    |
| 31         | Sideshow  | Bob        | Springfield |       -4869 | NULL    |
+------------+-----------+------------+-------------+-------------+---------+

+----------+-----------+------------+
| Quantity | ProductID | CustomerID |
+----------+-----------+------------+
|       20 | 99        | 21         |
|       14 | 12        | 21         |
|       10 | 66        | 21         |
|        1 | 99        | 31         |
|        2 | 12        | 31         |
|        4 | 78        | 31         |
|        2 | 66        | 31         |
|        2 | 66        | 15         |
|        2 | 78        | 15         |
|       19 | 66        | 14         |
+----------+-----------+------------+

我有3张表，一张是店内商品，一张是客户列表，一张表通过保存数量、产品ID和客户ID来绘制购买的产品和哪个客户之间的关系，它们都是各自表中的键

我想写一个声明，我可以将产品表中 NumberOfItemsOnHand 的值更新为 20，对于“Sideshow”(客户表中的名字)购买的每个产品

我试着用写语句:

UPDATE Product x
   SET x.NumberOfItemsOnHand = 20 
 WHERE x.ProductID = 
    (SELECT ProductID from BoughtBy WHERE BoughtBy.CustomerID = 
       (SELECT CustomerID FROM Customer WHERE FirstName = "Sideshow")
    );

我收到一条错误消息“子查询返回超过 1 行”，我不知道如何用另一种方式来做

Answer 1

• 您可以使用 Inner Join 来更有效地解决这个问题。
• 使用适当的关系连接所有表，仅考虑 customer.FirstName = "Sideshow" 的行，并更新相应产品的 NumberOfItemsOnHand 值。

改为尝试以下查询:

UPDATE Product AS p
JOIN BoughtBy AS b 
  ON b.ProductId = p.productID 
JOIN Customer AS c 
  ON c.CustomerID = b.CustomerID AND 
     c.FirstName = "Sideshow"
SET p.NumberOfItemsOnHand = 20