How to delete rows in merge statement for specific date where target doesnt match source

Question

I have a merge statement that runs every day as new data is dumped into my table every day, here is a sample of my code:

MERGE SQL_Backup as Target 
USING Temp as Source 
ON target.code = source.code 
WHEN MATCHED THEN update SET 
target.saledate = source.saledate, 
target.branchcode = source.branchcode 

WHEN NOT MATCHED BY TARGET 
THEN insert ( 
code, saledate
) 
values ( 
source.code, source.saledate 
)

    when not matched by source
      then delete;

however, that deletes my entire table, I am only wanting it to delete rows that were added today, any previous rows shouldnt be touched.

Here is an image of what both the source and target table look like:

Source/Target Table

I have a column labeled 'saledate' which contains the date of the data dumped in, and I have a unique column labeled 'code' which i used for the merge to detect duplicates.

Was wondering if theres a when not matched by source for saledate = today then delete or something?

Could you post a little bit more of the MERGE statement, especially the ON clause? And what the source and target look like? A small representative example of records from both tables. — MarcinJ, Apr 03 '19 at 23:24
Good day @CoreyWilliams, `(1)` Before we (or you) move to your next question and spend time on something that you might not read, PLEASE CLOSE YOUR PREVIOUS questions first. You have question from Mar 26 which got responses but you did not even respect the people that came to help you with respond to their attempt. Another question from Oct 9 '18 is still open as well. `(2)` As in your previous question you keep the mistake here. You do not provide us with the tools to reproduce your case. You should remember that we cannot read your mind and we do not see what you can see [to be continue]... — Ronen Ariely, Apr 04 '19 at 00:00
...[continue] Stories about your table or even images are not useful. Instead you should provide us **a script to create the table and to insert some sample data**. This will give us the tool to execute the script and built the table which is better than any story or description with images. **In addition, according to the sample data in the script you should provide a description** (with images if needed) **of the requested result**. Thanks, — Ronen Ariely, Apr 04 '19 at 00:04
Sorry, im still getting used to stack. Not sure how to delete old questions. I have updated the sample code in my question. Thankyou, and sorry again. — Corey Williams, Apr 04 '19 at 03:56
A must read for anyone using merge - Aaron Bertrand's [Use Caution with SQL Server's MERGE Statement](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) — Zohar Peled, Apr 04 '19 at 05:54

score 1 · Accepted Answer · answered Apr 04 '19 at 07:02

A MERGE statement allows to use only target's columns and columns in the comparison terms scope in the "WHEN NOT MATCHED BY SOURCE" clause.

For example if you want to filter by the dates in the target table then MERGE fits your case, but if you want to filter by the Dates of the Source table then this does not fit MERGE

Here is a full example using simple table

DROP TABLE IF EXISTS S
DROP TABLE IF EXISTS T
GO
CREATE TABLE S(
    id int,
    DT DATE
)
DROP TABLE IF EXISTS T
GO
CREATE TABLE T(
    id int,
    DT DATE
)

INSERT T(id, DT) values (1,'2000-01-01'),(2,'2000-01-01'),(3,'2019-01-01')
INSERT S(id, DT) values (3,'2000-01-01'),(4,'2000-01-01'),(5,'2019-01-01')
GO

SELECT * FROM T
SELECT * FROM S
GO

-- the following query will raise an error
MERGE T as Target USING S as Source
    ON Target.id = Source.id

    WHEN MATCHED THEN 
        UPDATE SET Target.id = Source.id + 1000
    WHEN NOT MATCHED BY TARGET THEN 
        insert (id, DT) values (Source.id, Source.DT)
    -- Only target columns and columns in the clause scope are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.
    -- Using the bellow wil raise an error:
     WHEN NOT MATCHED BY SOURCE AND Source.DT > '2005-01-01' THEN DELETE;
GO

-- the following query will work well
MERGE T as Target USING S as Source
    ON Target.id = Source.id

    WHEN MATCHED THEN 
        UPDATE SET Target.id = Source.id + 1000
    WHEN NOT MATCHED BY TARGET THEN 
        insert (id, DT) values (Source.id, Source.DT)
    -- The bellow will work OK, since I use condition on the TARGET
    WHEN NOT MATCHED BY SOURCE AND TARGET.DT > '2005-01-01' THEN DELETE;
GO

SELECT * FROM T
SELECT * FROM S
GO

Leon Yue · Answer 2 · 2019-04-04T07:36:13.533

You can set the AND <clause_search_condition> after WHEN NOT MATCHED BY SOURCE. Specifies the valid search condition, and then specifies that the rows matching rows in target_table are deleted.

For example, delete the rows where source.saledate=today.

MERGE SQL_Backup as Target 
USING Temp as Source 
ON target.code = source.code 
WHEN MATCHED THEN update SET 
target.saledate = source.saledate, 
target.branchcode = source.branchcode 

WHEN NOT MATCHED BY TARGET 
THEN insert ( 
code, saledate
) 
values ( 
source.code, source.saledate 
)

WHEN NOT MATCHED BY SOURCE AND  source.saledate = CONVERT(varchar(100), GETDATE(), 3)
THEN DELETE;

Your saledate format is "dd/mm/yy", so we need to convert the default dateformat.

Hope this helps.

How to delete rows in merge statement for specific date where target doesnt match source

2 Answers2