SQL如何关联chatmembers表更新chatmessages的chatId字段

• 字段名错误：第二个子查询使用了chatmembers表不存在的companyId字段，匹配outgoingId应该用usersId
• 作用域不明确：子查询内未显式指定incomingId、outgoingId属于外层的chatmessages表，不同数据库对该写法的兼容性不同，容易出现字段找不到的错误
• 关联冗余：chatmembers表本身已经存储了chatId和用户的对应关系，不需要额外关联chat表也能完成匹配，除非你需要额外校验chatId的合法性
• 多值返回风险：未做行数限制的情况下，如果两个用户同时属于多个聊天（比如同时存在私聊和群聊），子查询会返回多个chatId，直接触发更新报错

基础版（适配绝大多数场景） #

适合不需要额外校验chatId合法性的场景，性能更优：

UPDATE chatmessages m
SET m.chatId = (
    SELECT cm1.chatId
    FROM chatmembers cm1
    INNER JOIN chatmembers cm2 
        ON cm1.chatId = cm2.chatId
    WHERE cm1.usersId = m.incomingId
      AND cm2.usersId = m.outgoingId
    LIMIT 1 -- 避免多匹配场景下子查询返回多条数据报错
)
-- 可选：仅更新chatId为空的记录，防止覆盖已有合法数据
WHERE m.chatId IS NULL;

带chat表校验版 #

如果需要确保匹配到的chatId在chat表中真实存在，可使用该版本：

UPDATE chatmessages m
SET m.chatId = (
    SELECT c.chatId
    FROM chat c
    INNER JOIN chatmembers cm1 ON c.chatId = cm1.chatId
    INNER JOIN chatmembers cm2 ON cm1.chatId = cm2.chatId
    WHERE cm1.usersId = m.incomingId
      AND cm2.usersId = m.outgoingId
    LIMIT 1
)
WHERE m.chatId IS NULL;

内容的提问来源于stack exchange，提问作者Dann carlo Reformado