Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Delete the Existing data based on Where

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-02-18 : 02:41:50
i have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.
Let me explain my scenario Clearly

   1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.



How i need to proceed on this before 10 records are not getting deleted and new records adding for that one

My Example Code :


MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE
WHEN Col2 BETWEEN DATEADD(dd, - (DAY(cga.col1) - 1), cga.col2)
AND CASE
WHEN YEAR(cga.col1) = 9999
THEN cga.col1
ELSE DATEADD(d, - 1, DATEADD(m, DATEDIFF(m, 0, cga.col2) + 1, 0))
END
THEN dad.Col2
ELSE NULL
END
WHERE (
dad.AnchorDate = DateKey
OR DateKey IS NULL
)
) SOURCE
ON TARGET.Col1 = Source.Col1
AND TARGET.Col2 = Source.Col2
WHEN MATCHED
THEN
UPDATE
SET TARGET.Col1 = Source.Col1
,TARGET.Col2 = Source.Col2
WHEN NOT MATCHED
THEN
INSERT (
Col1
,Col2

)
VALUES (
Source.Col1
,Source.Col2
);

WHEN NOT MATCHED BY SOURCE
THEN
DELETE


P.V.P.MOhan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2014-02-18 : 10:22:55
1. You are missing a join from your example
2. dad.AnchorDate = NULL will always be incorrect, you may want to set this to something other than NULL to get this to work correctly. You can use IS NULL, but not in this case, but you could set all NULL dates to a certain date and use that as the basis for your join.
Go to Top of Page
   

- Advertisement -