MERGE查询从目标表中删除记录 [英] MERGE query to delete record from destination table

问题描述

朋友们。



我有以下MERGE查询。我在同一个数据库中有两个表。



1.作为目标表的库存和2.作为源表的INVENTORY_STAGE。

  MERGE  INVENTORY  AS  D 
 使用 INVENTORY_STAGE  AS  S 
  ON （D.CODE = S.CODE） 
  WHEN   NOT   MATCHED  
  THEN   INSERT （CODE，INV_DESCRIPTION，BVRVMODDATE） VALUES （S.CODE，S.INV_DESCRIPTION，S.BVRVMODDATE）
  WHEN   MATCHED   AND （S.BVRVMODDATE<> D.BVRVMODDATE）
 那么 更新 
  SET  CODE = S.CODE，INV_DESCRIPTION = S.INV_DESCRIPTION，BVRVMODDATE = S.BVRVMODDATE; 

以上查询检查数据是否与CODE值匹配，然后在目的地更新，否则将其插入目的地。



我的要求是数据在源表中不存在并存在于目标表中，那么记录应该从目标表中删除，



在上面的查询中它会发生什么变化？

Regard



Imrankhan

解决方案

读取MERGE（Transact-SQL） [ ^ ]似乎你必须使用什么时候没有匹配来源那么 。

我收到以下错误。



不匹配时不允许使用DELETE类型的操作'

MERGE声明的条款。



我的查询如下。

  MERGE  CUS2  AS   TARGET  
 使用 CUS1  AS   SOURCE  
  ON （ TARGET  .CU SNUMBER =  SOURCE  .CUSNUMBER）
  WHEN   MATCHED  
  THEN  更新 
  SET  
 CUSNUMBER =  SOURCE  .CUSNUMBER 
  WHEN  < span class =code-keyword> NOT   MATCHED   BY   TARGET  那么 
  DELETE  
  WHEN   NOT   MATCHED  
  THEN   INSERT （CUSNUMBER） VALUES （ SOURCE  .CUSNUMBER）; 

MERGE INVENTORY AS D

使用INVENTORY_STAGE为S

ON（D.CODE = S.CODE）

当没有与目标匹配时

然后INSERT（代码，INV_DESCRIPTION，BVRVMODDATE）值（S.CODE，S.INV_DESCRIPTION，S.BVRVMODDATE）

匹配时（S.BVRVMODDATE<> D.BVRVMODDATE）

那么更新

SET CODE = S.CODE，INV_DESCRIPTION = S.INV_DESCRIPTION，BVRVMODDATE = S.BVRVMODDATE

什么时候不来源匹配

然后删除;

Hi friends.

I have following MERGE query. I have two table in same database.

1. INVENTORY as destination table and 2. INVENTORY_STAGE as source table.

MERGE INVENTORY AS D
USING INVENTORY_STAGE AS S
ON (D.CODE = S.CODE)
WHEN NOT MATCHED
    THEN INSERT(CODE,INV_DESCRIPTION,BVRVMODDATE) VALUES(S.CODE,S.INV_DESCRIPTION,S.BVRVMODDATE)
WHEN MATCHED AND (S.BVRVMODDATE <> D.BVRVMODDATE)
    THEN UPDATE
    SET CODE=S.CODE,INV_DESCRIPTION=S.INV_DESCRIPTION,BVRVMODDATE=S.BVRVMODDATE;

The above query check if data is matched with CODE value then it is updated in destination otherwise it is inserted to destination.

My requirement is that is data is not exist in source table and exist in destination table then record should be deleted from destination table,

Where it would be change in above query?
Regard

Imrankhan

解决方案

Reading though MERGE (Transact-SQL)[^] it seems you have to use WHEN NOT MATCHED BY SOURCE THEN.

I am getting following error.

An action of type 'DELETE' is not allowed in the 'WHEN NOT MATCHED' clause of a
MERGE statement.

my query is like below.

MERGE CUS2 AS TARGET
USING CUS1 AS SOURCE
ON (TARGET.CUSNUMBER = SOURCE.CUSNUMBER)
WHEN MATCHED 
    THEN UPDATE
    SET 
    CUSNUMBER = SOURCE.CUSNUMBER
WHEN NOT MATCHED BY TARGET THEN
    DELETE
WHEN NOT MATCHED 
    THEN INSERT(CUSNUMBER) VALUES(SOURCE.CUSNUMBER);

MERGE INVENTORY AS D
USING INVENTORY_STAGE AS S
ON (D.CODE = S.CODE)
WHEN NOT MATCHED BY TARGET
THEN INSERT(CODE,INV_DESCRIPTION,BVRVMODDATE) VALUES(S.CODE,S.INV_DESCRIPTION,S.BVRVMODDATE)
WHEN MATCHED AND (S.BVRVMODDATE <> D.BVRVMODDATE)
THEN UPDATE
SET CODE=S.CODE,INV_DESCRIPTION=S.INV_DESCRIPTION,BVRVMODDATE=S.BVRVMODDATE
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

这篇关于MERGE查询从目标表中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！