通过联接一个或两个其他表来更新tweets.spam列

Question

考虑以下三个MySQL表：

tweets                        urls                    tweets_urls
---------------------------   ---------------------   ----------------
tweet_id text          spam   url_id  host     spam   tweet_id  url_id
---------------------------   ---------------------   ----------------
   1     I love cnn.com  0      16    cnn.com    0        1       16
   2     fox.com is fuk  0      17    fox.com    1        2       17
   3     love me!        0                                4       16
   4     blah cnn.com    0
   5     nice fox.com    0

我想根据tweets_urls更新tweets.spam，这意味着查询的输出应该是

tweets
---------------------------
tweet_id text          spam
---------------------------
   1     I love cnn.com  0  <-- tweets_urls tells me tweet_id 1 has url_id 16
   2     fox.com is fuk  1      in it, and the urls-table tells me that url 16
   3     love me!        0      is not spam (spam = 0)
   4     blah cnn.com    0
   5     nice fox.com    1

我希望我说得够清楚了。我一直在摆弄它，现在有了这样的东西。我知道这不可能是正确的，但我不知道如何重新开始。你是?

UPDATE tweets SET spam = (
  SELECT spam FROM urls
  LEFT JOIN tweets_urls
  WHERE urls.url_id = tweets_urls.url_id
)

任何帮助都将不胜感激:-)

Answer 1

对于给定的数据，此查询将返回结果集...

SELECT t.tweet_id
     , t.text
     , IFNULL(s.spam,t.spam) AS spam
  FROM tweets t
  LEFT
  JOIN ( SELECT tu.tweet_id, MAX(u.spam) AS spam
           FROM tweets_urls tu
           JOIN urls u ON u.url_id = tu.url_id
          WHERE u.spam = 1
          GROUP BY tu.tweet_id
       ) s
    ON s.tweet_id = t.tweet_id

但是我们已经做了一些假设，当给定的tweet_id在tweets_url中超过一行时，或者当没有匹配的url时，应该做什么。

如果你想要的是当一条推文被发现与任何标记为"spam=1“的url相关时，该推文被标记为"spam=1"，否则，该推文应该被标记为”spam=0“……

这将根据该规则为推文中的每一行设置垃圾邮件列...

UPDATE tweets t
  LEFT
  JOIN ( SELECT tu.tweet_id, MAX(u.spam) AS spam
           FROM tweets_urls tu
           JOIN urls u ON u.url_id = tu.url_id
          WHERE u.spam = 1
          GROUP BY tu.tweet_id
       ) s
    ON s.tweet_id = t.tweet_id
   SET t.spam = IFNULL(s.spam,0)

如果你想让垃圾邮件列保持原样(让它设置为任何设置)，并且只想更新当前值设置为0且应该设置为1的行，根据“匹配url具有spam=1"，您可以这样做：

UPDATE tweets t
  JOIN ( SELECT tu.tweet_id
           FROM tweets_urls tu
           JOIN urls u ON u.url_id = tu.url_id
          WHERE u.spam = 1
          GROUP BY tu.tweet_id
       ) s
    ON s.tweet_id = t.tweet_id
   SET t.spam = 1
 WHERE t.spam = 0

注意，在tweets表上的谓词，我们将只更新当前spam设置为0的行。注意，我们不需要引用urls表中垃圾邮件列的值，我们已经在测试它是否等于1，因此我们可以在为tweets.spam列赋值时使用文字1。还要注意，我们正在进行内部联接(而不是左外部联接)，因此，同样，我们将只更新将被赋值为1的行。

Answer 2

您忘记了将子选择与tweets表和连接中的ON子句关联起来：

UPDATE tweets SET spam = (
  SELECT spam FROM urls
  LEFT JOIN tweets_urls ON urls.url_id = tweets_urls.url_id
  WHERE tweets_urls.tweet_id = tweets.tweet_id
)

您还没有定义在以下情况下要执行的操作：

• tweets_urls中没有tweet_id
• 的条目，tweets_urls中有多个tweet_id

的条目

最后，顺便说一句，你确定要像这样更新吗？它听起来更像是您希望使用视图或存储过程生成的东西-除非urls和tweets_urls只是您现在为了帮助填充tweets表而添加的表，稍后将删除它们。