sql - 删除项目“WHEN NOT MATCHED BY SOURCE”

Question

需要一点帮助，请

这是我的表结构。表： Purchase_Master

Purchase_ID | POTYPE | PoNo
     1          M       1

表：TRN_Purchase_Details

Purchase_Detail_ID |  Purchase_ID |  Head_ID | Item_ID
       1                1               2         3
       2                1               2         3
       3                1               2         3

我将 XML 字符串传递给 SQL 以进行插入、更新和删除操作。

@PurchaseDetailsXML 的插入模式 XML 字符串

<documentelement>
    <TRN_Purchase_Details>
        <Purchase_Detail_ID> 0</Purchase_Detail_ID>
        <Purchase_ID></Purchase_ID>
        <Head_ID>2</Head_ID>
        <Item_ID>3</Item_ID>
    </TRN_Purchase_Details>
    <TRN_Purchase_Details>
        <Purchase_Detail_ID> 0</Purchase_Detail_ID>
        <Purchase_ID></Purchase_ID>
        <Head_ID>2</Head_ID>
        <Item_ID>3</Item_ID>
    </TRN_Purchase_Details>
    <TRN_Purchase_Details>
        <Purchase_Detail_ID> 0</Purchase_Detail_ID>
        <Purchase_ID></Purchase_ID>
        <Head_ID>2</Head_ID>
        <Item_ID>3</Item_ID>
    </TRN_Purchase_Details>
</documentelement>

@PurchaseDetailsXML 的更新模式 XML 字符串

<documentelement>
    <TRN_Purchase_Details>
        <Purchase_Detail_ID>1</Purchase_Detail_ID>
        <Purchase_ID>1</Purchase_ID>
        <Head_ID>2</Head_ID>
        <Item_ID>3</Item_ID>
    </TRN_Purchase_Details>
    <TRN_Purchase_Details>
        <Purchase_Detail_ID>3</Purchase_Detail_ID>
        <Purchase_ID>1</Purchase_ID>
        <Head_ID>2</Head_ID>
        <Item_ID>3</Item_ID>
    </TRN_Purchase_Details>
</documentelement>

这是“TRN_Purchase_Details”表的解析字符串：

 SELECT  Tbl.Col.value('Purchase_Detail_ID[1]', 'int') AS Purchase_Detail_ID ,                          
                Tbl.Col.value('Purchase_ID[1]', 'int') AS Purchase_ID ,                          
                Tbl.Col.value('Head_ID[1]', 'int') AS Head_ID ,                          
                Tbl.Col.value('Item_ID[1]', 'int') AS Item_ID 
        INTO    #TRN_Purchase_Details                          
        FROM    @PurchaseDetailsXML.nodes('/documentelement/TRN_Purchase_Details') Tbl ( Col ) 

这是我的插入更新删除代码：

MERGE TRN_Purchase_Details MTD                          
                USING                          
                    ( SELECT    Purchase_Detail_ID ,                          
                                Id AS Purchase_ID ,                          
                                Head_ID ,                          
                                Item_ID   
                      FROM      #TRN_Purchase_Details                          
                                LEFT JOIN @ChangeResult ON 1 = 1                          
                    ) AS TMTD                          
                ON MTD.Purchase_Detail_ID = TMTD.Purchase_Detail_ID                          
                    AND MTD.Purchase_ID = TMTD.Purchase_ID           
                WHEN MATCHED THEN                          
                    UPDATE SET MTD.Head_ID = TMTD.Head_ID ,                          
                              MTD.Item_ID = TMTD.Item_ID 

                WHEN NOT MATCHED BY TARGET THEN              
                    INSERT ( Purchase_ID ,                          
                             Head_ID ,                          
                             Item_ID
               )                          
                    VALUES ( Purchase_ID ,                          
                             Head_ID ,                          
                             Item_ID                          
                           )  

                            WHEN NOT MATCHED BY SOURCE   
                             AND   
        MTD.Purchase_Detail_ID NOT IN(SELECT Purchase_Detail_ID FROM #TRN_Purchase_Details)  
                            THEN   
                            DELETE ;

我想要的是 ：

我第一次在“TRN_Purchase_Details”中插入 3 个条目。第二次当我通过 2 个条目然后删除剩余的条目。

我的英语很差。

谢谢

标签： sqlsql-server