mysql更新一个表中的多行

b1zrtrql 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(356)

+-----------+---------------+-------------+---------------------+
| 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的值，对于“sideshow”购买的每个产品（customer表中的名字）
我试着用它来写陈述：

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行”，我不知道如何做它的另一种方式

mysql

来源：https://stackoverflow.com/questions/53102821/mysql-update-multiple-rows-in-one-table

2条答案

按热度按时间

eanckbw91#

您可以使用 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

赞(0）回复(0）举报 2021-06-18

z4bn682m2#

你试过：

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

赞(0）回复(0）举报 2021-06-18

我来回答

mysql更新一个表中的多行

2条答案

相关问题

热门标签

最新问答