none
SQL Merge Statement Explanantion

    Question

  • Hello Experts,

    We have an SQL Stored procedure that updates records in a table based with the results from a view.

    UPDATE RD     
    	SET     
    		RD.ColA = RTL.COLA,RD.COLB = RTL.COLB
    	FROM 
    		dbo.TAB1 as RD      
    	LEFT OUTER JOIN 
    		VIEW2
    	ON       
    		(RD.CID1  = View2.CID)

    The OUTER Join syntax was included to ensure that records that do not have matching values from view get updated with NULL Value.

    A team member suggested the use of the Merge Syntax

    MERGE INTO TABLE AS TARGET
    	USING
    	(
    		SELECT
    			COLA, COLB, CID
    		FROM VIEW2
    	)
    	AS SOURCE
    	ON SOURCE.CID= TARGET.CID 
    	WHEN MATCHED THEN
    		UPDATE
    		SET
    		COLA = SOURCE.COLA,
    		COLB= SOURCE.COLB
    		
    	WHEN NOT MATCHED BY SOURCE THEN
    		UPDATE
    		SET
    		COLA= NULL,
    		COLB= NULL
    		
    	;

    Which of the two approaches is best? The execution times are pretty much the same? Can somebody explain the advantage or disadvantage of using either approach?


    Regards, Dinesh

    Thursday, April 10, 2014 6:37 PM

Answers

  • The second approach is the best. UPDATE FROM is a proprietary syntax and if there are 1:M relations, the results are unpredictable. MERGE ensures that there can be only 1:1 match.

    In SQL 2008 and up MERGE is preferred syntax over UPDATE involving more than 1 table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 6:40 PM
    Moderator

All replies

  • The second approach is the best. UPDATE FROM is a proprietary syntax and if there are 1:M relations, the results are unpredictable. MERGE ensures that there can be only 1:1 match.

    In SQL 2008 and up MERGE is preferred syntax over UPDATE involving more than 1 table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 6:40 PM
    Moderator
  • Perfect, Thanks.

    Regards, Dinesh

    Monday, April 14, 2014 2:11 PM