MERGE-根据WHEN MATCHED块中的逻辑分别更新列值 [英] MERGE - UPDATE column values separately, based on logic in WHEN MATCHED block

问题描述

今天早些时候，我问了这个问题并得到了我一直在寻找的答案.现在我有一个后续问题:

我想要的东西:

我希望MERGE将目标表中每行的每个列值与源表中的对应值进行比较，并基于WHEN MATCHED AND块中以OR分隔的逻辑进行任何更新. /p>

如果WHEN MATCHED AND中用OR分隔的逻辑的任何，恐怕我编写的代码(如下图所示)将进行THEN UPDATE SET块中列出的更新.阻止是真的.

如果我的预感是正确的，那么您是否对如何重新编写代码以使其表现出我希望的方式有任何建议?

解决方案

由于没有您的数据，也不想从图像中重新输入查询，因此我创建了一个示例，演示了您想要的示例:

create table t (ID int not null,Col1 int null,Col2 int null)
create table s (ID int not null,Col1 int null,Col2 int null)

insert into t(ID,Col1,Col2) values (1,1,null),(2,null,2)
insert into s(ID,Col1,Col2) values (1,3,4),(2,5,6),(3,7,8)

;merge into t
using s
on t.ID = s.ID
when not matched then insert (ID,Col1,Col2) values (s.ID,s.Col1,s.Col2)
when matched then update
set Col1 = COALESCE(t.Col1,s.Col1),
Col2 = COALESCE(t.Col2,s.Col2)
;
select * from t

结果:

ID          Col1        Col2
----------- ----------- -----------
1           1           4
2           5           2
3           7           8

关键是要使用COALESCE避免更新已存在的列值(我认为这是您要实现的目标)

Earlier today, I asked this question and got the answer I was looking for. Now I have a follow-up question:

What I want:

I want the MERGE to compare each column value, per row, in the target table against the corresponding value in the source table, and make any updates based on the logic separated by OR in the WHEN MATCHED AND block.

I am afraid that the code I've written (pictured below) will make the updates listed in the THEN UPDATE SET block if any of the logic separated by OR in the WHEN MATCHED AND block is true.

If my hunch is correct, do you have any suggestions for how to re-write the code to have it behave like I want it to behave?

解决方案

Not having your data, and not wanting to re-type your query from an image, I created a sample that I think demonstrates what you want:

create table t (ID int not null,Col1 int null,Col2 int null)
create table s (ID int not null,Col1 int null,Col2 int null)

insert into t(ID,Col1,Col2) values (1,1,null),(2,null,2)
insert into s(ID,Col1,Col2) values (1,3,4),(2,5,6),(3,7,8)

;merge into t
using s
on t.ID = s.ID
when not matched then insert (ID,Col1,Col2) values (s.ID,s.Col1,s.Col2)
when matched then update
set Col1 = COALESCE(t.Col1,s.Col1),
Col2 = COALESCE(t.Col2,s.Col2)
;
select * from t

Result:

ID          Col1        Col2
----------- ----------- -----------
1           1           4
2           5           2
3           7           8

Where the key is to use COALESCE to avoid updating a column value if it already has one (which I think is what you're trying to achieve)

这篇关于MERGE-根据WHEN MATCHED块中的逻辑分别更新列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！