MySQL多表更新报错#1054：未知列'keywordssector.keywords'求助

需求说明 #

现有两个MySQL表：

• new_test2：字段包括title、noticeText、sectoradd
• keywordssector：字段包括keywords、sector

需要实现：在new_test2的title或noticeText中匹配keywordssector的keywords内容，将对应的sector编码以逗号分隔的形式追加到new_test2的sectoradd字段中。但执行脚本时持续报错#1054 - Unknown column 'keywordssector.keywords' in 'where clause'。

错误脚本问题分析 #

第一个脚本问题 #

UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, keywordssector.sector)
WHERE (
    new_test2.title LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
    OR new_test2.noticeText LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
) AND keywordssector.sector IS NOT NULL;

1. 未关联new_test2与keywordssector表，直接在WHERE中引用keywordssector.sector，MySQL无法识别该表字段。
2. 子查询(SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL)若返回多条结果，会触发Subquery returns more than 1 row错误，因为LIKE只能匹配单个值。

第二个脚本问题 #

UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, sector)
WHERE (
    new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
    OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%')
) AND keywordssector.sector IS NOT NULL
  AND new_test2.sectoradd NOT LIKE CONCAT('%', sector, '%')
JOIN keywordssector ON (new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
                        OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%'));

1. MySQL的UPDATE JOIN语法错误，JOIN子句必须放在WHERE之前，否则关联未完成时WHERE无法识别keywordssector的字段。
2. sector字段未指定表名，存在歧义（若两个表有同名字段会直接报错）。

正确实现脚本 #

方式一：逐匹配项追加（去重） #

该脚本会逐个匹配keywordssector的记录，将未存在于sectoradd中的sector追加进去：

UPDATE new_test2 t
JOIN keywordssector ks 
  ON t.title LIKE CONCAT('%', ks.keywords, '%') 
  OR t.noticeText LIKE CONCAT('%', ks.keywords, '%')
SET t.sectoradd = CASE
  WHEN t.sectoradd IS NULL OR t.sectoradd = '' THEN ks.sector
  WHEN t.sectoradd NOT LIKE CONCAT('%', ks.sector, '%') THEN CONCAT_WS(',', t.sectoradd, ks.sector)
  ELSE t.sectoradd
END
WHERE ks.sector IS NOT NULL;

方式二：一次性合并所有匹配的sector #

该脚本通过子查询聚合当前记录所有匹配的sector，去重后一次性更新到sectoradd：

UPDATE new_test2 t
SET t.sectoradd = (
  SELECT GROUP_CONCAT(DISTINCT ks.sector SEPARATOR ',')
  FROM keywordssector ks
  WHERE ks.sector IS NOT NULL
    AND (t.title LIKE CONCAT('%', ks.keywords, '%') OR t.noticeText LIKE CONCAT('%', ks.keywords, '%'))
)
WHERE EXISTS (
  SELECT 1 FROM keywordssector ks
  WHERE ks.sector IS NOT NULL
    AND (t.title LIKE CONCAT('%', ks.keywords, '%') OR t.noticeText LIKE CONCAT('%', ks.keywords, '%'))
);

内容的提问来源于stack exchange，提问作者GurusGuru