Update rows for generic vs specific inputs

Question

I have a table with structure, all not nullable. table1: col1, col2, col3, col4

Table1 Data:

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

A client application sends data update in JSON format. Col1 to 3 are keys with value in col4. Values in col3 can be null, which means it applies to all rows matching just col1&2. The data may contain specific entries for col3 that need be applied instead of the one with null. I load the data into @input with the same column names.

@input loaded:

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

I am trying to come up with a query that will update table1 from @input but don't know how to join the tables such that specific col3 entries are updated from rows with col3 values in @input.

update table1
set col4=inp.col4
from table1 tbl1
inner join @input inp on tbl1.col1=inp.col1 and tbl1.col2=inp.col2 and ?

Is there a single query I can write to get the below result?

Table1 Data updated:

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

Test below did not provide the desired result:

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;

Query output:

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

Desired output:

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

Answer 1

There might be faster ways, but I'd go for a CROSS APPLY TOP 1 solution, something like:

SELECT  *, col4 AS col4_old
INTO #data
FROM
(
    VALUES  (N'kc11', N'kc21', N'kc31', 100)
    ,   (N'kc11', N'kc21', N'kc32', 125)
    ,   (N'kc11', N'kc22', N'kc31', 150)
    ,   (N'kc11', N'kc22', N'kc32', 105)
    ,   (N'kc11', N'kc22', N'kc33', 106)
    ,   (N'kc11', N'kc22', N'kc34', 107)
    ,   (N'kc11', N'kc22', N'kc35', 108)
) t (col1,col2,col3,col4)


UPDATE  t
SET col4 = y.col4
FROM    #data t
CROSS APPLY (
    SELECT  TOP 1 *
    FROM    (
        VALUES  (N'kc11', N'kc21', NULL, 160)
        ,   (N'kc11', N'kc22', NULL, 175)
        ,   (N'kc11', N'kc22', N'kc32', 125)
        ) n (col1,col2,col3,col4)
    WHERE   n.col1 = t.col1
    AND n.col2 = t.col2
    AND (n.col3 IS NULL OR n.col3 = t.col3) -- Sanity check
    ORDER BY CASE 
            WHEN n.col3 = t.col3 THEN 0 -- Exact match
            ELSE 1 -- col3 is NULL
        END
    ) y

Basically, I try to find the best match by taking TOP 1 ordered by exact match and then null.

To avoid updating too many rows, I've added AND (n.col3 IS NULL OR n.col3 = t.col3), this makes sure we won't update from any other random row.

Final result:

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