时间戳

首页 > 解决方案 > 如何在合并或更新操作期间比较源表和目标表之间的三个列

sql - 如何在合并或更新操作期间比较源表和目标表之间的三个列

问题描述

我有两张桌子，contribution(Member_number,Salyear,Salmonth,ReceiptDate) 和contribution_update(Member_number,Salyear,Salmonth,ReceiptDate)。Receiptdate表中的列contribution具有空值。Member_Number我想通过比较,和匹配项的组合来更新列Salyear，然后更新。SalmonthReceiptdate

我编写了以下代码，但它合并了 o 值。

任何对如何改进或重写脚本有想法的人。

我从这段代码开始

MERGE INTO CONTRIBUTION cgt
USING (select MEMBER_NUMBER,SALYEAR,SALMONTH,RECEIVED_DATE from CONTRIBUTION_UPDATE  )cga
ON (cgt.MEMBER_NUMBER=cga.MEMBER_NUMBER AND cgt.SALYEAR=cga.SALYEAR AND cgt.SALMONTH=cga.SALMONTH)
WHEN matched then
update 
SET cgt.RECEIPTDATE=cga.RECEIVED_DATE;

然后我发现该表contribution_update有重复项（Gor 消息无法获得稳定的行集..）。我重新创建了没有重复的表。

MERGE INTO Contribution M
      USING
      (Select Member_Number,Salyear,Salmonth,ReceiptDate From
(select MEMBER_NUMBER,SALYEAR,SALMONTH,RECEIptDATE,row_number() over (partition by MEMBER_NUMBER,SALYEAR,SALMONTH
 order by null ) as qry from Contribution_update)where qry=1) vu
              ON  (Vu.Member_Number = M.Member_Number and M.Salyear=Vu.Salyear and M.Salmonth=Vu.Salmonth )
  WHEN MATCHED
  THEN
  UPDATE
  SET  M.Receiptdate = Vu.ReceiptDate;
  WHERE M.Salyear=Vu.Salyear and M.Salmonth=Vu.Salmonth;

查询应该合并目标表中的许多行。

标签： sql

解决方案

你不需要那个WHERE条件，它是由条件处理的。该语句将仅对匹配的行执行 - 因此源和目标之间的行 where和是相同的。UPDATEMERGEONWHEN MATCHEDMEMBER_NUMBERSALYEARSALMONTH

让我们假设源 -ContributionUpdate看起来像这样：

MEMBER_NUMBER SALYEAR     SALMONTH    RECEIVED_DATE
------------- ----------- ----------- -------------
1             2019        1           2019-10-10
2             2019        1           2019-10-20

和目标 -Contribution像这样：

MEMBER_NUMBER SALYEAR     SALMONTH    RECEIVED_DATE
------------- ----------- ----------- -------------
1             2019        1           NULL

目标中有一个匹配行，成员 1，2019 年，第 1 个月。如果您只想RECEIVED_DATE从源更新匹配的年份，这就足够了：

MERGE INTO Contribution t
USING ContributionUpdate s
   ON s.MEMBER_NUMBER = t.MEMBER_NUMBER
  AND s.SALYEAR = t.SALYEAR
  AND s.SALMONTH = t.SALMONTH

WHEN MATCHED
THEN UPDATE 
 SET RECEIVED_DATE = s.RECEIVED_DATE;

(1 row affected)

如果您还想将源中缺少的一行插入到目标中（对于成员 2），则需要使用WHEN NOT MATCHED BY TARGET，如下所示：

MERGE INTO Contribution t
USING ContributionUpdate s
   ON s.MEMBER_NUMBER = t.MEMBER_NUMBER
  AND s.SALYEAR = t.SALYEAR
  AND s.SALMONTH = t.SALMONTH

WHEN MATCHED
THEN UPDATE 
 SET RECEIVED_DATE = s.RECEIVED_DATE

WHEN NOT MATCHED BY TARGET
THEN INSERT
(
   MEMBER_NUMBER,
   SALYEAR      ,
   SALMONTH     ,
   RECEIVED_DATE
)
VALUES
(
   s.MEMBER_NUMBER,
   s.SALYEAR      ,
   s.SALMONTH     ,
   s.RECEIVED_DATE
);

(2 rows affected)

结果，该Contribution表将如下所示：

MEMBER_NUMBER SALYEAR     SALMONTH    RECEIVED_DATE
------------- ----------- ----------- -------------
2             2019        1           2019-10-20
1             2019        1           2019-10-10

推荐阅读