UPDATE，使用CASE语句，忽略NULL值

Question

当我尝试更新一个超过100,000行的表时，下面的代码表现不佳。

我有大约2500个值，其中序列中的第一个值为NULL，因此行和以下序列没有更新。

此外，大约5000行也没有更新，因为它们在两个表中的第二个表(表1 = dbo)中不存在。ABC/table 2 = dbo.abc_d)

关于第二个问题，我可以为该表编写第二个查询，但我被困在第一个问题上。如果您有任何关于改进此查询的建议，我将不胜感激。

UPDATE dbo.abc abc
SET    vcn1 = ( CASE
WHEN abc.chan_id1 IS NOT NULL THEN abc.chan_id1
WHEN abc.chan_id2 IS NOT NULL THEN abc.chan_id2
WHEN abc.chan_id3 IS NOT NULL THEN abc.chan_id3
--omitting this chuck for readability
WHEN abc_d.chan_id34 IS NOT NULL THEN abc_d.chan_id34
WHEN abc_d.chan_id35 IS NOT NULL THEN abc_d.chan_id35
WHEN abc_d.chan_id36 IS NOT NULL THEN abc_d.chan_id36
END ),
vcn2 = ( CASE
WHEN abc.chan_id1 IS NOT NULL AND abc.chan_id1 != abc.vcn1 THEN abc.chan_id1
WHEN abc.chan_id2 IS NOT NULL AND abc.chan_id2 != abc.vcn1 THEN abc.chan_id2
WHEN abc.chan_id3 IS NOT NULL AND abc.chan_id3 != abc.vcn1 THEN abc.chan_id3
--omitting this chuck for readability
WHEN abc_d.chan_id34 IS NOT NULL AND abc_d.chan_id34 != abc.vcn1 THEN abc_d.chan_id34
WHEN abc_d.chan_id35 IS NOT NULL AND abc_d.chan_id35 != abc.vcn1 THEN abc_d.chan_id35
WHEN abc_d.chan_id36 IS NOT NULL AND abc_d.chan_id36 != abc.vcn1 THEN abc_d.chan_id36
END )
FROM   dbo.abc_d abc_d
WHERE  abc.abc_id = abc_d.abc_id; 

Answer 1

可以使用左连接来解决第二个问题。你的第一个问题不是问题:

UPDATE dbo.abc
SET    vcn1 = coalesce( abc.chan_id1, 
abc.chan_id2, 
abc_d.chan_id34,
abc_d.chan_id35
),
vcn2 = ( CASE
WHEN abc.chan_id1 IS NOT NULL AND abc.chan_id1 != abc.vcn1 THEN abc.chan_id1
WHEN abc.chan_id2 IS NOT NULL AND abc.chan_id2 != abc.vcn1 THEN abc.chan_id2
WHEN abc_d.chan_id34 IS NOT NULL AND abc_d.chan_id34 != abc.vcn1 THEN abc_d.chan_id34
WHEN abc_d.chan_id35 IS NOT NULL AND abc_d.chan_id35 != abc.vcn1 THEN abc_d.chan_id35
END )
FROM  dbo.abc abc 
left join dbo.abc_d abc_d
on  abc.abc_id = abc_d.abc_id; 

这是DBFiddle演示