SQL实现Observation表关联记录互设related_observation_id

Observation表的记录来自Result表（observation_id = result_id），表中每条记录都有同表内的对应关联记录。需要为这些关联记录互相设置对应的related_observation_id。

当前表结构 #

Observation表 #

Result表 #

期望的Observation表 #

解决方案 #

方法1：使用UPDATE JOIN（适用于MySQL、PostgreSQL 9.2+等支持该语法的数据库） #

通过Result表的linking_id分组匹配关联记录，关联Observation表批量更新：

UPDATE Observation o
JOIN Result r1 ON o.observation_id = r1.result_id
JOIN Result r2 ON r1.linking_id = r2.linking_id AND r1.result_id != r2.result_id
SET o.related_observation_id = r2.result_id;

方法2：使用子查询（通用多数数据库） #

通过子查询找到每条记录对应的关联ID，再执行更新：

UPDATE Observation
SET related_observation_id = (
    SELECT r2.result_id
    FROM Result r1
    JOIN Result r2 ON r1.linking_id = r2.linking_id AND r1.result_id != r2.result_id
    WHERE r1.result_id = Observation.observation_id
);

特殊场景处理（如Oracle） #

Oracle需使用MERGE语句完成更新：

MERGE INTO Observation o
USING (
    SELECT r1.result_id AS src_id, r2.result_id AS related_id
    FROM Result r1
    JOIN Result r2 ON r1.linking_id = r2.linking_id AND r1.result_id != r2.result_id
) t
ON (o.observation_id = t.src_id)
WHEN MATCHED THEN
    UPDATE SET o.related_observation_id = t.related_id;

注意事项 #

• 确保每个linking_id下恰好只有两条记录，否则上述SQL会因返回多条结果报错。若存在一组超过两条的情况，需明确关联规则，比如取组内最大/最小ID，修改子查询为SELECT MAX(r2.result_id)...或SELECT MIN(r2.result_id)...。

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