按目标子句合并，不更新所需的列

Question

我需要写一个SQL指令(不是一个过程)，更新一个日期列和插入行，如果一个ID已经存在于目标。

目标表:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2050-12-31

新传入数据:

ID  DT_DEBVAL   DT_FINVAL
2   2021-09-08  2050-12-31
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

所需输出:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2021-09-07 -- current date minus 1 day 
2   2021-09-08  2050-12-31 
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

我做了和这里一样的事情。这是我的代码版本:

insert into t1 (id, DT_DEBVAL, DT_FINVAL) 
select  t.id, t.DT_DEBVAL, t.DT_FINVAL from ( 
merge t1 As Target 
using (select * from t2) AS Source
on Target.id=Source.id and Target.dt_finval=Source.dt_finval
when matched then
update set Target.DT_FINVAL=DATEADD(day, -1, getdate())
when not matched by target then insert (id, dt_debval, dt_finval) values (Source.id, Source.dt_debval, Source.dt_finval) OUTPUT $ACTION as Act, Inserted.* ) t 
where t.Act  = 'Update'
select * from t1;

得到如下输出:

ID  DT_DEBVAL   DT_FINVAL
1   2021-09-01  2050-12-31
2   2021-09-01  2021-09-07
2   2021-09-01  2021-09-07
3   2021-09-08  2050-12-31
4   2021-09-08  2050-12-31

可以看到，ID=2的行并不完全正确。知道它为什么会这样反应吗?

Answer 1

您的问题是，当您引用内存驻留表inserted时，您引用的是正在更新的行，并返回新更新的值，因此您只是再次插入相同的数据。您需要引用来自源的值，例如

OUTPUT $ACTION as Act, inserted.ID, inserted.DT_FINVAL AS DT_DEBVAL, source.DT_FINVAL

所以你的完整查询应该是:

insert into t1 (ID, DT_DEBVAL, DT_FINVAL) 
select  t.ID, GETDATE(), t.DT_FINVAL from ( 
merge t1 As Target 
using (select * from t2) AS Source
on Target.ID=Source.ID and Target.DT_FINVAL=Source.DT_FINVAL
when matched then
update set Target.DT_FINVAL=DATEADD(day, -1, GETDATE())
when not matched by target then 
insert (ID, DT_DEBVAL, DT_FINVAL) 
values (Source.ID, Source.DT_DEBVAL, Source.DT_FINVAL) 
OUTPUT $ACTION as Act, inserted.id, source.DT_FINVAL) t 
where t.Act  = 'Update';


select * from t1 order by ID;

SQL Fiddle示例

潜在的替代:

insert into t1 (ID, DT_DEBVAL, DT_FINVAL) 
select  t.ID, t.DT_DEBVAL, t.DT_FINVAL from ( 
merge t1 As Target 
using (select * from t2) AS Source
on Target.ID=Source.ID and Target.DT_FINVAL=Source.DT_FINVAL
when matched then
update set Target.DT_FINVAL=DATEADD(day, -1, Source.DT_DEBVAL)
when not matched by target then 
insert (ID, DT_DEBVAL, DT_FINVAL) 
values (Source.ID, Source.DT_DEBVAL, Source.DT_FINVAL) 
OUTPUT $ACTION as Act, inserted.id, Source.DT_DEBVAL, source.DT_FINVAL) t 
where t.Act  = 'Update';

---

我想说的是，你的解决方案可能过于复杂，虽然我并没有强烈地远离合并(我会避免在大多数情况下，但我还没有找到一个合适的替代方案来捕获输出中不属于插入的列)，这是一个我绝对不会打扰它的地方，因为它没有提供任何优势。

你的逻辑总是:

• 更改任何现有"活动"的结束日期
• 从传入数据中插入所有不存在的值

需要以下查询:

BEGIN TRANSACTION;
UPDATE t1 WITH (UPDLOCK, SERIALIZABLE)
SET    DT_FINVAL = DATEADD(DAY, -1, t2.DT_DEBVAL)
FROM   t1
INNER JOIN t2
ON t2.ID = t1.ID
AND t2.DT_FINVAL = t1.DT_FINVAL;
INSERT t1 (ID, DT_DEBVAL, DT_FINVAL)
SELECT t2.ID, t2.DT_DEBVAL, t2.DT_FINVAL
FROM   t2
WHERE  NOT EXISTS (SELECT 1 FROM t1 WHERE t1.ID = t2.ID AND t2.DT_FINVAL = t1.DT_FINVAL);
COMMIT TRANSACTION;

我认为除了避免合并和它的错误，这使得意图更加清晰(至少对我来说，我认为自己非常熟悉MERGE语法)。它还减少了操作的总数，你现在只做一个更新和一个插入，而不是一个合并(insert &