mysql - 使用两个 ROW 值的合并更新 mysql 列

Question

我的数据库中有一个表user_brand，它存储有关用户购买的品牌的信息。假设我的表不依赖于另一个表，表如下所示:

User_ID  |   Brand      | meta_key
---------+--------------+-------------
1        |   Killer     |  Name
1        |   Lewis      |  Name
1        |   Pepe       |  Name
1        |   Cloth      |  Product
2        |   Samsung    |  Name
2        |   Motorolla  |  Name
2        |   CellPhone  |  Product
3        |   Acer       |  Name
3        |   Laptop     |  Product

现在我想更新我的品牌列，以便输出如下所示，如果 meta_key 重复，对我来说没问题，我对此不太担心。

User_ID  |  Brand           
---------+-----------------------
1        |  Killer, Lewis, Pepe
2        |  Samasung, Motorolla
3        |  Nisaan

我实现了在选择查询中获取输出，即

SELECT User_ID, GROUP_CONCAT(Brand) 
FROM user_brand 
WHERE meta_key = "Name" 
GROUP BY User_Id

但是尝试了很多方法来更新那个特定的列但都失败了。

一些尝试过的查询是:

UPDATE user_brand  
SET Brand = (SELECT max 
             FROM 
                 (SELECT GROUP_CONCAT(Brand) AS max 
                  FROM user_brand  
                  WHERE meta_key = "Name" 
                  GROUP BY User_id) AS t)

UPDATE user_brand AS t1 
JOIN 
    (SELECT 
         GROUP_CONCAT(brand) AS max 
     FROM user_brand 
     WHERE meta_key = "Name" 
     GROUP BY User_id) AS t2 
SET t1.Brand = t2.max

如有任何帮助，我们将不胜感激。

Answer 1

尝试下面 - 您必须添加 ON 子句

UPDATE user_brand  t1 
JOIN 
(SELECT User_id,GROUP_CONCAT(brand) as max FROM user_brand where meta_key = "Name" 
group by User_id 
) AS t2 on t1.user_id=t2.user_id 
SET t1.Brand = t2.max