如何使用join根据另一个表更新mysql中的多个值

debugcn 发表于 Dev

库纳尔

我有两个表产品和过滤器。我的产品表如下：-

id    name    color_id    size_id    composition_id
 1    Test      Black     60x60 CM       Cotton
 2    Test2     Red       60X90 CM      Acryllic

我的过滤器表如下：-

id    filter_name
 1      Black
 2      Red
 22     60x60 CM 
 23     60X90 CM
 61     Cotton
 62     Acryllic

我的预期输出如下：-

id    name    color_id    size_id    composition_id
 1    Test       1            22           61
 2    Test2      2            23           62

我尝试了以下仅适用于 color_id 的查询。查询如下：-

UPDATE products
INNER JOIN filters ON products.color_id = filters.filter_name
SET products.color_id = filters.id

我想在一个更新语句中更新所有“color_id,size_id,composition_id”。可能我们需要使用Case Statements。任何人都可以帮忙吗？

缺口

您可以这样做的一种方法是使用一组相关的子查询，一个用于您需要查找的每一列。我们使用 aCOALESCE以便如果找不到该值，我们保留以前的值：

UPDATE products p
  SET color_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.color_id), color_id),
      size_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.size_id), size_id),
      composition_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.composition_id), composition_id)

然后你可以SELECT * FROM products：

id  name    color_id    size_id composition_id
1   Test    1           22      61
2   Test2   2           23      62

dbfiddle 上的演示

您还可以通过UPDATE ... JOIN查询获得相同的结果，这可能更有效：

UPDATE products p
LEFT JOIN filters f1 ON f1.filter_name = p.color_id
LEFT JOIN filters f2 ON f2.filter_name = p.size_id
LEFT JOIN filters f3 ON f3.filter_name = p.composition_id
SET color_id = COALESCE(f1.id, color_id),
    size_id = COALESCE(f2.id, size_id),
    composition_id = COALESCE(f3.id, composition_id)

dbfiddle 上的演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。