mysql - MySQL 更新一张表中的多行
问题描述
+-----------+---------------+-------------+---------------------+
| 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来绘制购买的产品与哪个客户之间的关系,这些都是各自表中的键
我想做一个语句,我可以将“Sideshow”(客户表中的名字)购买的每个产品的产品表中 NumberOfItemsOnHand 的值更新为 20
我试着用写语句:
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 行”,我不知道如何以另一种方式进行
解决方案
- 您可以
Inner Join
改为使用更有效的方法来解决此问题。 - 使用适当的关系连接所有表,并仅考虑那些行 where
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
推荐阅读
- javascript - 使用 Reactjs 选择并上传多个图像
- android - 收听 HDMI 输入端口
- swift - 按下按钮 Swift 后打开可滚动的 pdf
- java - ./gradlew 操作无法完成。找不到 Java 运行时
- kubernetes - 如何在 K8s 服务中从容器中公开特定路径?
- java - 无法访问传递给 ScriptEngine 的 Java 对象的属性
- flutter - 如何通过 Flux 将对象更改传达给 Sping MongoDB Reactive 中的 Websocket
- asp.net - 将 DateTime 从 ASP.NET 发送到 angularjs 作为结构,而不是字符串
- docker - Nginx 反向代理映射到多个前端和后端
- swift - SwiftUI 视图更新