MySQL根据部分查询结果更新列

Question

我正在尝试根据 MySQL 查询更新一个表列。问题是查询结果分为两列，但我只需要更新类别列。

未分类

--------------------------------------------------
| id | category_lookup      | category1          |
--------------------------------------------------
| 1  | Chino                | *to be found*      |
--------------------------------------------------
| 2  | Rainjacket           | *to be found*      |
--------------------------------------------------

title_match

--------------------------------------------------
| id | title                | category1          |
--------------------------------------------------
| 1  | Chino                | Trousers / Jeans   |
--------------------------------------------------
| 2  | Rainjacket           | Jackets / Coats    |
--------------------------------------------------

查询

SELECT distinct not_categorized.id,  category_lookup
FROM not_categorized
left JOIN title_match ON  not_categorized.category_lookup LIKE CONCAT('%', title_match.title, '%')
group by id; 

这给了我正确的结果。根据“Chino”一词，类别应为“牛仔裤/长裤”。

查询结果

-------------------------
| id | category         | 
-------------------------
| 1  | Trousers / Jeans |
-------------------------
| 2  | Jackets / Coats  |
-------------------------

现在如何更新“not_characterized”表中的“category1”列？

这不起作用:

update not_categorized
set category1 = **QUERY**

Answer 1

查看您的数据，您可以使用基于标题和类别查找的内部联接

  update not_categorized
  inner join title_match on not_categorized.category_lookup =  title_match.title
  set not_categorized.category1 = title_match.category1