通用与特定输入下Table1行更新的SQL查询求解

现有非空表table1（字段col1、col2、col3、col4），客户端将更新数据传入@input表，col1-col3作为匹配键，col4为更新值。规则如下：

• @input中col3为null时，匹配table1中相同col1&col2的所有行
• 特定col3值的条目优先级更高（若某行同时匹配col3=null和特定col3的input条目，以特定col3的input值为准）

当前尝试的更新语句未达到预期效果，求可实现目标的单条SQL语句。

初始数据（table1） #

col1  col2  col3  col4
======================
kc11  kc21  kc31  100
kc11  kc21  kc32  125
kc11  kc22  kc31  150
kc11  kc22  kc32  105
kc11  kc22  kc33  106
kc11  kc22  kc34  107
kc11  kc22  kc35  108

更新数据（@input） #

col1  col2  col3  col4
======================
kc11  kc21  null  160
kc11  kc22  null  175
kc11  kc22  kc32  125

期望更新后的数据（table1） #

col1  col2  col3  col4
======================
kc11  kc21  kc31  160
kc11  kc21  kc32  160
kc11  kc22  kc31  175
kc11  kc22  kc32  125
kc11  kc22  kc33  175
kc11  kc22  kc34  175
kc11  kc22  kc35  175

当前未达预期的测试代码 #

declare @table1 table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4  varchar(10)
);

insert @table1 (col1,col2,col3,col4) values ('kc11','kc21','kc31','100');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc21','kc32','125');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc31','150');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc32','105');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc33','106');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc34','107');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc35','108');

declare @input table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4  varchar(10)
);

insert @input (col1,col2,col3,col4) values ('kc11','kc21',  null,'160');
insert @input (col1,col2,col3,col4) values ('kc11','kc22',  null,'175');
insert @input (col1,col2,col3,col4) values ('kc11','kc22','kc32','125');

select * from @table1;
update @table1
set col4=inp.col4
from @table1 tbl1
inner join @input inp on tbl1.col1=inp.col1 and tbl1.col2=inp.col2 
    and (inp.col3 is null or inp.col3 = tbl1.col3)

select * from @table1;

解决方案 #

原代码的问题在于：匹配到多个input条目的行（比如kc11,kc22,kc32会同时匹配col3=null和col3=kc32的条目）会被多次更新，最终结果取决于SQL执行顺序，无法保证优先级。

我们需要先为每个table1的行找到优先级最高的input条目，再执行更新。可以通过CTE（公共表表达式）实现：

declare @table1 table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4  varchar(10)
);

insert @table1 (col1,col2,col3,col4) values ('kc11','kc21','kc31','100');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc21','kc32','125');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc31','150');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc32','105');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc33','106');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc34','107');
insert @table1 (col1,col2,col3,col4) values ('kc11','kc22','kc35','108');

declare @input table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4  varchar(10)
);

insert @input (col1,col2,col3,col4) values ('kc11','kc21',  null,'160');
insert @input (col1,col2,col3,col4) values ('kc11','kc22',  null,'175');
insert @input (col1,col2,col3,col4) values ('kc11','kc22','kc32','125');

select * from @table1;

-- 正确的更新语句
WITH RankedInputs AS (
    SELECT 
        inp.col1, inp.col2, inp.col3, inp.col4,
        tbl1.col3 AS tbl_col3,
        -- 标记优先级：特定col3的优先级为1，col3=null的为2（数字越小优先级越高）
        ROW_NUMBER() OVER (
            PARTITION BY tbl1.col1, tbl1.col2, tbl1.col3
            ORDER BY CASE WHEN inp.col3 IS NOT NULL THEN 1 ELSE 2 END
        ) AS rn
    FROM @table1 tbl1
    JOIN @input inp 
        ON tbl1.col1 = inp.col1 
        AND tbl1.col2 = inp.col2 
        AND (inp.col3 IS NULL OR inp.col3 = tbl1.col3)
)
UPDATE tbl1
SET col4 = ri.col4
FROM @table1 tbl1
JOIN RankedInputs ri 
    ON tbl1.col1 = ri.col1 
    AND tbl1.col2 = ri.col2 
    AND tbl1.col3 = ri.tbl_col3
WHERE ri.rn = 1;

select * from @table1;

逻辑说明 #

1. RankedInputs CTE：为每个table1行匹配到的所有input条目标记优先级，col3非空的条目优先级高于col3为null的条目。
2. 更新语句：只取每个table1行对应的优先级最高（rn=1）的input条目值进行更新，确保特定col3的更新不会被批量更新覆盖。

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