编写MERGE语句以查找不匹配的值 [英] Write a MERGE Statement for mis-matching values

问题描述

我有一个表tblstars_new，该表每周从客户端更新一次.我还有另一个表tblstars，该表需要从tblstars_new导入新数据和更新数据.

I have a table, tblstars_new, which is updated weekly from the client. I have another table, tblstars, which needs to import new and updated data from tblstars_new.

在tblstars_new中查找tblstars中不存在的行，然后将其添加到tblstars中很简单.

Finding rows in tblstars_new that do not exist in tblstars, and then adding then to tblstars is simple.

但是，我还需要在tblstars_new中查找其中PandA_Code列已更改的行，然后在tblstars中更新相同的行.

But, I also need to find rows in tblstars_new in which the column PandA_Code has changed, and then update the identical row in tblstars.

此查询告诉我tblstars_new中的哪些行具有已更改的PandA_Code，需要在tblstars中进行更新.

This query tells me which rows from tblstars_new have PandA_Code's that have changed and need to be updated in tblstars.

SELECT 
    sn.*
FROM
    tblstars_new sn
    JOIN tblstars s ON sn.Student_ID_Number = s.Student_ID_Number AND sn.PandA_Code != s.PandA_Code

我正在尝试找出将进行更改的MERGE语句.正如我在Prod中所做的那样，我无法真正玩转.两个问题:

I'm trying to figure out a MERGE statement that will make the changes. As I'm doing in Prod, I can't really play around. Two questions:

1)是否可以在不实际进行更改的情况下看到更改?

1) Is it possible to see the changes without actually doing them?

2)下面的MERGE语句正确吗?

2) Is the MERGE statement below correct?

BEGIN TRAN;
MERGE tblstars AS T         -- Target
USING tblstars_new AS S     -- Source
ON 
    (T.Student_ID_Number = S.Student_ID_Number AND T.PandA_Code != S.PandA_Code) 
WHEN NOT MATCHED BY TARGET 
    THEN 
    UPDATE SET T.PandA_Code = S.PandA_Code
OUTPUT $action;
ROLLBACK TRAN;
GO 

推荐答案

这可以通过简单地使用join而不是merge语句来完成:

This is simply done by using join instead of merge statement:

UPDATE T SET T.PandA_Code = S.PandA_Code FROM tblstart T 
INNER JOIN tblstars_new P ON P.Student_ID_Number = T.Student_ID_Number
WHERE T.PandA_Code <> S.PandA_Code 

这篇关于编写MERGE语句以查找不匹配的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！