update使用group_contact

Aug 3, 2011 • {"display_name"=>"pensz", "login"=>"pensz", "email"=>"email", "url"=>""}

以下讨论针对mysql。

想要达到的目的：

UPDATE users AS u
LEFT JOIN orders AS o ON o.user_id = u.user_id
SET u.orders = GROUP_CONCAT(DISTINCT o.order_id)

上面的sql无法运行，即使加上GROUP BY也是不行的

UPDATE users AS u
LEFT JOIN orders AS o ON o.user_id = u.user_id
SET u.orders = GROUP_CONCAT(DISTINCT o.order_id)
GROUP BY u.user_id

一种的不用临时表的解决方法为：
UPDATE users AS u
SET u.orders = (
SELECT GROUP_CONCAT(DISTINCT o.order_id)
FROM orders o
WHERE o.user_id = u.user_id
)