SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Delete the Existing data based on Where
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/18/2014 :  02:41:50  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/18/2014 :  10:22:55  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000