MERGE 语句 DELETE SQL Server 中的替代项

Question

我有一个使用 T-SQL 合并语句的查询。由于性能问题，我正在使用IF Exists Update和If Not Exists Insert重新编写查询。我能够毫无问题地编写插入/更新。但我无法处理删除。有人可以帮我吗？

这是示例

---SAMPLE MERGE STATEMENT
MERGE 
member_topic AS target
USING 
someOtherTable AS source
ON 
target.mt_member = source.mt_member 
WHEN MATCHED THEN 
UPDATE SET target.mt_notes = source.mt_notes
WHEN NOT MATCHED THEN 
INSERT (mt_member, mt_topic, mt_notes) VALUES (source.mt_member, source.mt_notes)
WHEN NOT MATCHED BY SOURCE THEN
DELETE member_topic;

--UPDATE
UPDATE T SET T.mt_notes = S.mt_notes
FROM member_topic T 
JOIN someOtherTable S ON T.mt_member=S.mt_member

--INSERT
INSERT INTO member_topic(mt_member, mt_topic, mt_notes)   
SELECT mt_member, mt_topic, mt_notes 
FROM someOtherTable S 
WHERE NOT EXISTS(SELECT 1 
FROM member_topic T 
WHERE T.mt_member=S.mt_member)

如何处理

WHEN NOT MATCHED BY SOURCE THEN
DELETE member_topic;

在单个 DELETE 语句中。

Answer 1

要在proc 中嵌入在 begin 和 end 之间的示例脚本

MERGE dbo.Tablet AS TARGET
USING dbo.QueryView AS SOURCE 
ON ( 
TARGET.[ID] = SOURCE.[ID] 
)
WHEN MATCHED 
THEN
UPDATE SET
TARGET.[ID] = SOURCE.[ID]
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ID, [Name]  )
VALUES (SOURCE.[ID], SOURCE.[Name]  )
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Answer 2

试试

DELETE T
FROM member_topic T 
WHERE NOT EXISTS(SELECT 1 
FROM someOtherTable S 
WHERE T.mt_member=S.mt_member)

Answer 3

DELETE t
FROM member_topic t
LEFT JOIN someOtherTable s ON t.mt_member = s.mt_member
WHERE s.mt_member IS NULL