mysql - 使用联接更新多个表

Question

我有以下查询，效果很好:

$query = "UPDATE
            mpt
        JOIN
            compounds
        ON
            compounds.mpt = mpt.id
        SET
            mpt.supervisor_comments = " . $this->db->escape($comments['mpt']) . "
        WHERE
            compounds.id = " . $id;

并更新链接到化合物表中具有特定 ID 的行的 mpt 表。

然后我尝试扩展查询以一次更新两个字段，但是，此时我被告知:

Unknown column 'mpt.id' in 'on clause'

UPDATE mpt, bpt JOIN compounds ON compounds.mpt = mpt.id AND compounds.bpt = bpt.id SET mpt.supervisor_comments = 'abcd' WHERE compounds.id = 5

尽管在前面的示例中它运行得很好...有什么建议或建议吗？

$query = "UPDATE
               mpt, bpt
           JOIN
               compounds
           ON
               compounds.mpt = mpt.id AND compounds.bpt = bpt.id
           SET
               mpt.supervisor_comments = " . $this->db->escape($comments['mpt']) . "
           WHERE
               compounds.id = " . $id;

Answer 1

JOIN 的优先级运算符比表列表中的逗号更高，因此在加入 bpt 时与 compounds表mpt尚未加入。明显的修复计数是像这样放置括号:

UPDATE (mpt, bpt) JOIN compounds ON ...