CODEWITHSUNDEEP
sqlsql-servert-sql
Ram
Ram

Reputation: 91

MERGE statement DELETE alternative in SQL Server

I have a Query using T-SQL MERGE Statement. Due to performance issues I am re writing the Query using IF Exists Update and If Not Exists Insert. I am able to write Insert/Update without any issue. But I am unable to handle the DELETE. Can some one please help me on this?

Here is the sample

---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)

How to handle 

WHEN NOT MATCHED BY SOURCE THEN
DELETE member_topic;

in single DELETE Statement.

Upvotes: 0

Views: 1353

Answers (3)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

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

Upvotes: 0

Serg
Serg

Reputation: 22811

Try 

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

Upvotes: 0

Ven
Ven

Reputation: 2014

a sample script to be embedded between begin and end in proc

   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;

Upvotes: 1

Related Questions