sql - 根据 SQL Server 中其他表的值更新多个列

Question

我需要根据“参考”表中的值更新“主”表中的多个列。例如，在给出的示例数据中，@MAIN_TABLE 应根据连接条件 (main.ID_1=ref) 更新为第一条记录 (ID_1=10, ID_2=100) 的 ABC_VAL=11111, KLM_VAL=33333, XYZ_VAL=22222 .ID_1 和 main.ID_2=ref.ID_2)，以及字段 ref.FLD_NAME 的值。即，如果 ref.FLD_NAME='ABC'，则 main.ABC_VAL 应使用相应的 ref.FLD_VAL 进行更新。

DECLARE @MAIN_TABLE TABLE ([ID_1] INT, [ID_2] INT, [ABC_VAL] INT, [KLM_VAL] INT, [XYZ_VAL] INT) 
insert @MAIN_TABLE values
(10,100,NULL,NULL,NULL),
(10,200,NULL,NULL,NULL),
(30,300,NULL,NULL,NULL)


DECLARE @REF_TABLE TABLE ([ID_1] INT, [ID_2] INT, [FLD_NAME] varchar(3),[FLD_VAL] INT) 
insert @REF_TABLE values
(10,100,'ABC',11111),
(10,100,'XYZ',22222),
(10,100,'KLM',33333),
(20,200,'ABC',88888),
(30,300,'KLM',55555)

标签： sqlsql-server

Answer 1

您可以使用以下脚本使用所需的结果更新列 -

DECLARE @MAIN_TABLE TABLE ([ID_1] INT, [ID_2] INT, [ABC_VAL] INT, [KLM_VAL] INT, [XYZ_VAL] INT) 
insert @MAIN_TABLE values
(10,100,NULL,NULL,NULL),
(10,200,NULL,NULL,NULL),
(30,300,NULL,NULL,NULL)


DECLARE @REF_TABLE TABLE ([ID_1] INT, [ID_2] INT, [FLD_NAME] varchar(3),[FLD_VAL] INT) 
insert @REF_TABLE values
(10,100,'ABC',11111),
(10,100,'XYZ',22222),
(10,100,'KLM',33333),
(20,200,'ABC',88888),
(30,300,'KLM',55555)

SELECT * FROM @MAIN_TABLE
SELECT * FROM @REF_TABLE

UPDATE MT
SET MT.ABC_VAL = B.ABC_VAL,
MT.XYZ_VAL = B.XYZ_VAL,
MT.KLM_VAL = B.KLM_VAL
FROM @MAIN_TABLE MT
INNER JOIN (
    SELECT A.ID_1,A.ID_2,
    SUM(A.ABC_FLD_VAL) AS [ABC_VAL],
    SUM(A.XYZ_FLD_VAL) AS [XYZ_VAL],
    SUM(A.KLM_FLD_VAL) AS [KLM_VAL]
    FROM (
        SELECT DISTINCT MT.ID_1,MT.ID_2,
        CASE WHEN rt.FLD_NAME = 'ABC' THEN FLD_VAL  ELSE NULL END 'ABC_FLD_VAL',
        CASE WHEN rt.FLD_NAME = 'XYZ' THEN FLD_VAL ELSE NULL END 'XYZ_FLD_VAL',
        CASE WHEN rt.FLD_NAME = 'KLM' THEN FLD_VAL ELSE NULL END 'KLM_FLD_VAL'

        FROM @MAIN_TABLE MT
        INNER JOIN @REF_TABLE RT ON RT.ID_1 = MT.ID_1 AND RT.ID_2 = MT.ID_2
    ) A
    GROUP BY  A.ID_1,A.ID_2
) B
ON MT.ID_1 = B.ID_1
AND MT.ID_2 = B.ID_2

SELECT * FROM @MAIN_TABLE