MySQL UPDATE 记录被另一个表引用
我需要更新其主键被另一个表中的特定列引用的所有记录的特定列。 （外键关系已经建立。） 与我想要做的类似的是：
表 1：三明治
| Sandwich(varchar) [Primary Key] | Requested(bool) |
|---|---|
| Salami | 1 |
| Pastrami | Null |
| Ham | 0 |
| Turkey | 1 |
Table 2：Requests
| Character(varchar) | Sandwich (varchar) [三明治的外键] |
|---|---|
| Charlie | Ham |
| Linus | Turkey |
| Marcy | Ham |
| Lucy | Salami |
| Snoopy | Pastrami |
我希望三明治中的每个空值。请求更新为 1（如果有）是请求中引用其主键的记录。例如，由于 Pastrami 对于三明治.requested 和史努比请求的 Pastrami 具有空值，因此我希望将 Pastrami 的三明治.requested 更新为 1，以便 Sandwiches 看起来如下：
表 1：Sandwiches
| Sandwich(varchar) [Primary Key] | Requested(bool) |
|---|---|
| 萨拉米 | 1 |
| 熏牛肉 | 1 |
| 火腿 | 0 |
| 火鸡 | 1 |
我知道如何：
SELECT SANDWICHES.SANDWICH, SANDWICHES.REQUESTED FROM SANDWICHES INNER JOIN REQUESTS ON SANDWICHES.SANDWICH = REQUESTS.SANDWICH WHERE REQUESTED = NULL
返回：
| 三明治 | 请求 |
|---|---|
| 熏牛肉 | Null |
告诉我我想要哪些更新，但说我的数据集太大，无法手动执行所有更新。我如何创建更新命令来更新每场比赛？
I need to update a specific column of all records whose primary key is referenced by a specific column in another table. (The foreign key relationship is already set up.)
An analog to what I'm trying to do would be this:
Table 1: Sandwiches
| Sandwich(varchar) [Primary Key] | Requested(bool) |
|---|---|
| Salami | 1 |
| Pastrami | Null |
| Ham | 0 |
| Turkey | 1 |
Table 2: Requests
| Character(varchar) | Sandwich (varchar) [Foreign Key to Sandwiches] |
|---|---|
| Charlie | Ham |
| Linus | Turkey |
| Marcy | Ham |
| Lucy | Salami |
| Snoopy | Pastrami |
I want every null value in sandwiches.requested updated to 1 if there is a record in requests referencing it's primary key. E.g., since Pastrami has a null value for sandwiches.requested and Snoopy requested Pastrami, I want sandwiches.requested updated to 1 for Pastrami, so that Sandwiches looks thus:
Table 1: Sandwiches
| Sandwich(varchar) [Primary Key] | Requested(bool) |
|---|---|
| Salami | 1 |
| Pastrami | 1 |
| Ham | 0 |
| Turkey | 1 |
I know how to:
SELECT SANDWICHES.SANDWICH, SANDWICHES.REQUESTED FROM SANDWICHES INNER JOIN REQUESTS ON SANDWICHES.SANDWICH = REQUESTS.SANDWICH WHERE REQUESTED = NULL
which returns:
| Sandwich | Requested |
|---|---|
| Pastrami | Null |
telling me which ones I want to update, but say my data set were too large to manually perform all the updates. How would I create an update command to update every match?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最直接的，就是：
Most straightforwardly, just: