合并成无行 [英] Merge Into with no rows

问题描述

有没有一种使用指令的方法:

Is there a way to use the instruction:

MERGE INTO MySchema.MyTable AS Target
USING (VALUES
........
)

什么都没有，而不是点?通常情况下，您有一些类似的列表(firstValue，SecondValue，...，LastValue)，对于要合并的每一行都有一个列表，但我希望能够编写不包含任何行的指令，以使该行的DELETE部分MERGE删除所有行.

With nothing instead of the dots? Usually you have there something like a list of (firstValue, SecondValue,...,LastValue), one for each row you want to merge but I'd like to be able to write the instruction with NO rows so that the DELETE part of the MERGE deletes all the rows.

这是因为我正在使用一个存储过程，该存储过程会自动创建MERGE指令，但有时我从中开始的表为空.

This is because I am using a stored procedure that creates the MERGE instruction automatically but sometimes the table that i am starting from is empty.

我当然尝试过:

MERGE INTO MySchema.MyTable AS Target USING (VALUES) 

但不被接受.

示例:

MERGE INTO [dbo].[MyTable] AS Target
USING (VALUES (1,'Attivo') ,(2,'Disabilitato') ,(3,'Bloccato') ) AS Source ([IDMyField],[MyField]) ON (Target.[IDMyField] = Source.[IDMyField])
WHEN MATCHED AND ( NULLIF(Source.[MyField], Target.[MyField]) IS NOT NULL OR NULLIF(Target.[MyField], Source.[MyField]) IS NOT NULL)
THEN UPDATE SET [MyField] = Source.[MyField]
WHEN NOT MATCHED BY TARGET
THEN INSERT([IDMyField],[MyField]) VALUES(Source.[IDMyField],Source.[MyField]) 
WHEN NOT MATCHED BY SOURCE
THEN DELETE; 

推荐答案

如果您正在生成内部查询，并且外部查询与预定义的ID字段匹配，则可以执行以下操作:

If you're generating the inside query, and the outside query is matching on an predefined ID field, the following will work:

MERGE INTO tester AS Target
USING (

select null as test1 --generate select null, alias as your id field
) as SOURCE on target.test1 = source.test1
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

对于您的特殊情况:

MERGE INTO table1 AS Target
USING (

values(null)
) as SOURCE(id) on target.id = source.id
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

这篇关于合并成无行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！