MERGE query to delete record from destination table

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 28-Sep-11 22:52pm

Imran Khan Pathan

Updated 9-Jan-17 21:49pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Answer 1 · 2011-09-28T23:09:00

Solution 1

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

Posted 28-Sep-11 23:09pm

André Kraak

Imran Khan Pathan · Answer 2 · 2011-09-29T02:51:00

Solution 2

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.

SQL

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

Posted 29-Sep-11 2:51am

Imran Khan Pathan

Comments

André Kraak 29-Sep-11 8:53am

Please to do not post additions to your question as a solution. If you wish to change your question use the Improve Question button.
Using an solution like this will give the people the impression that your question has been answered and perhaps not look at it, causing you not to get an answer to your question.

Please move the content of this solution to your question and remove the solution. Thank you.

André Kraak 29-Sep-11 9:00am

Replace "BY TARGET" with "BY SOURCE".

Member 12710388 · Answer 3 · 2017-01-09T21:49:00

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;