Oracle中如何借助其他表行数据查询并更新主表？

各表结构与数据说明 #

源表（sourcetable） #

id  col1   col2  col3   col4  col5
1   Peter  DEM   Scott  Neo   Morpheus

索引表（indextable） #

id  D    T  fromfieldsource  tofieldmaster
1   123     col1             A
1   123     col1             B

主表（mastertable） #

id   A     B          C    D 
Neo        Morpheus

主表更新SQL语句 #

根据索引表的映射规则（将源表col1的值更新到主表A、B列，通过源表col4与主表id关联匹配），提供两种可行的Oracle语句：

方式1：UPDATE语句 #

UPDATE mastertable m
SET (A, B) = (
    SELECT 
        MAX(CASE WHEN it.tofieldmaster = 'A' THEN st.col1 END),
        MAX(CASE WHEN it.tofieldmaster = 'B' THEN st.col1 END)
    FROM sourcetable st
    JOIN indextable it ON st.id = it.id
    WHERE st.col4 = m.id
)
WHERE EXISTS (
    SELECT 1 
    FROM sourcetable st
    JOIN indextable it ON st.id = it.id
    WHERE st.col4 = m.id
);

方式2：MERGE语句（更直观） #

MERGE INTO mastertable m
USING (
    SELECT 
        st.col4 AS master_id,
        MAX(CASE WHEN it.tofieldmaster = 'A' THEN st.col1 END) AS val_A,
        MAX(CASE WHEN it.tofieldmaster = 'B' THEN st.col1 END) AS val_B
    FROM sourcetable st
    JOIN indextable it ON st.id = it.id
    GROUP BY st.col4
) src
ON (m.id = src.master_id)
WHEN MATCHED THEN
    UPDATE SET 
        m.A = src.val_A,
        m.B = src.val_B;

操作说明 #

1. 关联逻辑：通过源表col4的Neo值匹配主表id，确保只更新目标行
2. 映射处理：用CASE语句根据索引表的字段映射规则，将源表col1的值分别赋值给主表A、B列
3. Toad操作：将上述SQL复制到Toad的SQL编辑器，点击执行按钮即可。执行前建议备份主表数据，避免误操作

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