如何编写含SELECT、JOIN、GROUP BY的UPDATE查询，正确填充用户表字段

你的SQL查询出现所有用户被设置为同一值的原因是没有将主表users和子查询结果通过用户ID关联，导致所有符合条件的用户都匹配到了子查询中的任意一条结果（最终取到同一个max_datetime）。

修改后的查询（基于原语句调整） #

直接在原查询的WHERE子句中添加用户ID的关联条件，确保每个用户只匹配自己的最大推文时间：

UPDATE users
SET datetime_modified_at = Grouped.max_datetime
FROM (
    SELECT users.id, MAX(tweets.datetime) AS max_datetime 
    FROM users 
    INNER JOIN tweets ON users.id = tweets.user_id
    WHERE users.datetime_created_at IS NOT NULL AND users.datetime_modified_at IS NULL
    GROUP BY users.id
) AS Grouped
WHERE users.datetime_created_at IS NOT NULL 
  AND users.datetime_modified_at IS NULL
  AND users.id = Grouped.id; -- 关键：建立用户ID的关联

更高效的写法（优化子查询逻辑） #

如果数据量较大，建议先从tweets表筛选目标用户的最大推文时间，再关联更新，减少重复关联users表的开销：

UPDATE users
SET datetime_modified_at = Grouped.max_datetime
INNER JOIN (
    SELECT user_id, MAX(datetime) AS max_datetime 
    FROM tweets
    WHERE user_id IN (
        SELECT id FROM users 
        WHERE datetime_created_at IS NOT NULL AND datetime_modified_at IS NULL
    )
    GROUP BY user_id
) AS Grouped ON users.id = Grouped.user_id
WHERE users.datetime_created_at IS NOT NULL 
  AND users.datetime_modified_at IS NULL;

补充说明 #

如果存在符合条件但没有发布过推文的用户，上述INNER JOIN会跳过这些用户（保持datetime_modified_at为NULL）。如果需要给这类用户设置默认值（比如用户创建时间），可以将INNER JOIN改为LEFT JOIN，并使用COALESCE函数处理：

UPDATE users
SET datetime_modified_at = COALESCE(Grouped.max_datetime, users.datetime_created_at)
LEFT JOIN (
    SELECT user_id, MAX(datetime) AS max_datetime 
    FROM tweets
    WHERE user_id IN (
        SELECT id FROM users 
        WHERE datetime_created_at IS NOT NULL AND datetime_modified_at IS NULL
    )
    GROUP BY user_id
) AS Grouped ON users.id = Grouped.user_id
WHERE users.datetime_created_at IS NOT NULL 
  AND users.datetime_modified_at IS NULL;

内容的提问来源于stack exchange，提问作者Mark Johnson