update使用group_contact

On 八月 3, 2011, in 技术记录, by pensz

以下讨论针对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
)

Tagged with: