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屋!