MySQL 如何在 UPDATE 语句中连接两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9957171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join two tables in an UPDATE statement?
提问by Pr0no
Consider the following tables: "users" and "tweets"
考虑以下表格：“用户”和“推文”
user_id name tweet_id user_id tweet spam
----------------- ----------------------------------
1 SUSPENDED 1 1 lorem ipsum 0
2 foo 2 1 dolor 0
3 bar 3 2 samet 0
4 SUSPENDED 4 1 stunitas 0
5 3 hello 0
6 4 spamzz! 0
I want to update the "tweets" table by marking all tweets made by SUSPENDED users, as spam. So in the above example, tweets with tweet_id 1, 2, 4 and 6 would be marked as spam by updating the "spam" value from 0 to 1.
我想通过将 SUSPENDED 用户发布的所有推文标记为垃圾邮件来更新“推文”表。因此，在上面的示例中，tweet_id 为 1、2、4 和 6 的推文将通过将“垃圾邮件”值从 0 更新为 1 来标记为垃圾邮件。
I'm having trouble joining the two tables. Until now, I've only had to join in SELECT statements, but this seems more troublesome:
我在连接这两个表时遇到问题。到目前为止，我只需要加入 SELECT 语句，但这似乎更麻烦：
UPDATE tweets SET spam = 1 WHERE tweets.user_id = users.user_id
AND users.name = 'SUSPENDED'
This surely isn't working...who could point me in the right direction?
这肯定行不通……谁能指出我正确的方向？
回答by Michael Berkowski
You're on the right track, but you need to specify a JOINbetween the tables:
您走在正确的轨道上，但您需要JOIN在表之间指定一个：
UPDATE tweets JOIN users ON tweets.user_id = users.user_id
SET tweets.spam = 1
WHERE users.name = 'SUSPENDED'
回答by VoteyDisciple
This should do it:
这应该这样做：
UPDATE tweets
INNER JOIN users ON (users.user_id = tweets.user_id)
SET spam=1
WHERE users.name='SUSPENDED'
You can generally use JOINthe same in an UPDATEas you can in a SELECT. You wouldn't be able to join a table to itself in an UPDATE, and there are some other little quirks, but for basic stuff like this it'll work as you'd expect.
您通常可以在 a 中使用JOIN与在 a 中相同UPDATE的内容SELECT。您将无法在 中将表与自身连接起来UPDATE，并且还有其他一些小怪癖，但对于像这样的基本内容，它会按您的预期工作。
回答by Dan P
Started answering before verifying the type of server you are using. I know this works in MS SQL Server. I am not sure about MySQL however. Don't have MySQL installed so give it a try.
在验证您使用的服务器类型之前开始回答。我知道这适用于 MS SQL Server。但是，我不确定 MySQL。没有安装 MySQL，所以试一试。
UPDATE tweets SET
spam = 1
FROM tweets INNER JOIN users ON users.user_id = tweets.user_id
WHERE users.name = 'SUSPENDED'
回答by Md Jamiruddin
Suppose, you have 3 tables and need to update one column from one tablewith another table. There are several ways to do this.
Please look into this queryfor example.
假设你有3个表，并需要从一个更新一列table与另一个table。有几种方法可以做到这一点。query例如，请查看此内容。
update Table1 T1, Table2 T2, Table3 T3 set T1.field_name = T3.field_name where (T1.field_name = T2.field_user_name) and (T2.field_id = T3.entity_id);
Relation between tablesmust be maintained carefully.
之间的关系tables必须小心维护。
