mysql - 如何在 MySQL 5.x 中使用多个联接更新 SELECT

Question

我需要更新包含分类的表，如果存在匹配的文章，则将文章 ID 添加到每个记录中。通过匹配ref_id、dial_ids来定义。

现在我不知何故陷入了 UPDATE SELECT 查询。

SQL Fiddle with Structure

结构表分类:

ID, article_id

结构表文章:

article_id, dial_id, ref_id

结构表classifieds_attr:

attr_group_id, cl_id, value

以下查询为我提供了匹配的classified_ids:

-- get all classified ids where we have matching articles
SELECT
a.article_id,
ca_d.cl_id,
a.dial_id,
a.ref_id
FROM 
    `articles` a
-- get classified_ids where ref_id match the article
INNER JOIN classifieds_attr ca_r ON ca_r.attr_group_id = 8 AND ca_r.value = a.ref_id
-- filter down for matching dial
INNER JOIN classifieds_attr ca_d ON ca_r.cl_id = ca_d.cl_id AND ca_d.attr_group_id = 9 AND ca_d.value = a.dial_id

更改 SQL 以使用匹配的记录更新表分类:

-- update classified table and insert article_id if matching article available
UPDATE c, a
SET 
    c.article_id = ca_d.cl_id,
    c.date_edited = ca.date_edited
FROM 
    classifieds c,
    articles a
-- get classified_ids where ref_id match the article
INNER JOIN classifieds_attr ca_r ON ca_r.attr_group_id = 8 AND ca_r.value = a.ref_id
-- filter down for matching dial
INNER JOIN classifieds_attr ca_d ON ca_r.cl_id = ca_d.cl_id AND ca_d.attr_group_id = 9 AND ca_d.value = a.dial_id

语法有些错误。如何更改 SQL 查询以使用相应的article_ids 更新分类？

Answer 1

MySQL 没有用于 UPDATE 的 FROM 子句。我不知道 classified 是如何连接到其他表的，因此请填写 JOIN 条件。查询如下所示:

UPDATE articles a JOIN
       classifieds_attr ca_r
       ON ca_r.attr_group_id = 8 AND ca_r.value = a.ref_id JOIN
       classifieds_attr ca_d
       ON ca_r.cl_id = ca_d.cl_id AND ca_d.attr_group_id = 9 AND ca_d.value = a.dial_idclassifieds c JOIN
       classifieds c
       ON c.? = ?  -- whatever the right JOIN condition is
    SET c.article_id = ca_d.cl_id,
        c.date_edited = ca.date_edited