我知道这是一个老问题,但这是我为实施ETL作业准备的示例。似乎需要else语句才能正常工作。这是在具有时间表的SQL Server 2017中完成的。
-- match to target on id
merge into Flags with (tablock) as target
using #Flags as source
on source.id = target.id
-- on match update row
when matched then
update set
-- flags 1-4 can't be switched back to false from a true state
target.flag1 = (
case when source.flag1 >= target.flag1
then source.flag1
else target.flag1
end ),
target.flag2 = (
case when source.flag2 >= target.flag2
then source.flag2
else target.flag2
end ),
target.flag3 = (
case when source.flag3 >= target.flag3
then source.flag3
else target.flag3
end ),
target.flag4 = (
case when source.flag4 >= target.flag4
then source.flag4
else target.flag4
end ),
-- flag 5 can only be true if flag 4 is be false
target.flag5 = (
case when target.flag4 = 1
or source.flag4 = 1
then 0
when source.flag5 >= target.flag5
then source.flag5
else target.flag5
end ),
-- flag 6 can only be true if flags 4 & 5 are both false
target.flag6 = (
case when (target.flag4 = 1
or target.flag5 = 1) or
(source.flag4 = 1
or source.flag5 = 1)
then 0
when source.flag6 >= target.flag6
then source.flag6
else target.flag6
end ),
-- flag 7 can only be true if flags 4-6 are all false
target.flag7 = (
case when (target.flag4 = 1
or target.flag5 = 1
or target.flag6 = 1) or
(source.flag4 = 1
or source.flag5 = 1
or source.flag6 = 1)
then 0
when source.flag7 >= target.flag7
then source.flag7
else target.flag7
end ),
-- normal updates
target.flag8 = source.flag8,
target.flag9 = source.flag9
-- no match insert row
when not matched by target then
insert (
id,
flag1,
flag2,
flag3,
flag4,
flag5,
flag6,
flag7,
flag8,
flag9
) values (
source.id,
source.flag1,
source.flag2,
source.flag3,
source.flag4,
source.flag5,
source.flag6,
source.flag7,
source.flag8,
source.flag9
)
;
else
,那么case
语句会返回NULL
,因此值被设置为NULL
。 - Gordon LinoffNULL
,ELSE提供了一个捕获所有的方法,告诉它对于未匹配任何WHEN语句的情况该怎么做(记住,您可以有多个WHEN语句)。 - Bridge