如何在UPDATE语句中联接两个表? [英] How to join two tables in an UPDATE statement?
问题描述
请考虑以下表格:用户"和推文"
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
我想通过将暂停"用户所做的所有推文标记为垃圾邮件来更新推文"表.因此,在上述示例中,通过将垃圾邮件"值从0更新为1,将tweet_id为1、2、4和6的tweet标记为垃圾邮件.
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.
我无法连接两个表.到目前为止,我只需要加入SELECT语句,但这似乎更加麻烦:
I'm having trouble joining the two tables. Until now, I've only had to join in SELECT statements, but this seems more troublesome:
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?
推荐答案
您在正确的轨道上,但是需要在表之间指定JOIN
:
You're on the right track, but you need to specify a JOIN
between the tables:
UPDATE tweets JOIN users ON tweets.user_id = users.user_id
SET tweets.spam = 1
WHERE users.name = 'SUSPENDED'
这篇关于如何在UPDATE语句中联接两个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!