mysql - 需要帮助内连接多个表并使用 MySQL 中的最后一个值行

Question

我有如下 3 个表:

评论

|id| |uid| |tid|

轨道

|id| |uid|

通知

|id| |from| |tox|

如何UPDATE通知和SET tox等于其相对tracks.id的tracks.uid code> 是否等于最后一个 comments.tid 值？

我尝试过但没有成功:

UPDATE notifications SET tox = (
  SELECT uid FROM tracks 
  INNER JOIN comments ON tracks.id = comments.tid ORDER BY comments.tid DESC LIMIT 1
  WHERE comments.tid=tracks.id)
WHERE tox = 0 ORDER BY id DESC LIMIT 1;

更新

首先，我按照建议编辑并移动了末尾的 ORDER BY 。之后我收到了另一个错误1052 - 字段列表中的列“typeid”不明确。

我是这样解决的:

UPDATE `notifications` SET `tox` = (
  SELECT tracks.uid FROM `tracks` 
  INNER JOIN `comments` ON tracks.id = comments.tid 
  ORDER BY comments.id DESC LIMIT 1)
WHERE `tox` = 0 ORDER BY `id` DESC LIMIT 1;

Answer 1

我认为你的问题是子查询的语法:

UPDATE notifications
    SET tox = (SELECT uid
               FROM tracks INNER JOIN
                    comments
                    ON tracks.id = comments.tid
               WHERE comments.tid=tracks.id
               ORDER BY comments.tid DESC
               LIMIT 1
              )
    WHERE tox = 0
    ORDER BY id DESC
    LIMIT 1;

ORDER BY 始终位于 WHERE 之后。