1

The code below is successfully updating an existing record but does not insert the new record where the details do not match and I don't understand why not as the code compiles and does not throw any error messages. I'm sure I have missed something obvious. For reference I using SQL Server 2008 with a case-sensitive collation but I don't see how that makes a difference. I have other MERGE cases that work just fine, it's just this one that doesn't play nicely.

To see the update working, modify the colourid in the DEBUG variable declarations to be the same as the value in the insert statement.

BEGIN TRY 
    DROP TABLE #adr_test
END TRY
BEGIN CATCH
    -- nothing to drop
END CATCH

CREATE TABLE #adr_test
(
    style NVARCHAR(5)
    ,size_id INT
    ,colour_id INT
    ,cost MONEY
)

INSERT INTO #adr_test (style, size_id, colour_id, cost) 
VALUES ('ADR01', 100, 101, 99.99)

/*DEBUG*/
DECLARE @style NVARCHAR(5) = 'ADR01'
DECLARE @sizeid INT = 100
DECLARE @colourid INT = 999
DECLARE @ctncost MONEY = 1.50
/*END DEBUG*/

MERGE #adr_test AS Tgt
USING (SELECT style, size_id, colour_id, cost
       FROM #adr_test                           
       WHERE style = @style
         AND size_id = @sizeid
         AND colour_id = @colourid) AS Src ON Src.style = Tgt.style
                                           AND Src.size_id = Tgt.size_id
                                           AND Src.colour_id = Tgt.colour_id

WHEN MATCHED AND Tgt.cost <> @ctncost 
   THEN
      UPDATE SET Tgt.cost = @ctncost

WHEN NOT MATCHED BY TARGET 
   THEN 
      INSERT (style, size_id, colour_id, cost)
      VALUES (@style, @sizeid, @colourid, @ctncost);


SELECT * FROM #adr_test
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aaron Reese
  • 131
  • 11
  • 1
    You are MERGEing into the same table that you are SELECTing from, it will never NOT MATCH. – RBarryYoung Oct 29 '18 at 15:53
  • yes it will. in the above example, the USING query will return a NULL dataset as there is no record with this @colourid. Therefore it is NOT MATCHED – Aaron Reese Oct 29 '18 at 16:10
  • Must read: [Use Caution with SQL Server's MERGE Statement](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) by Aaron Bertrand. – Zohar Peled Oct 29 '18 at 16:37
  • 2
    I suspect that since your source is empty, the `NOT MATCHED` condition is not triggered – Code Different Oct 29 '18 at 16:43
  • Your @ColorID varable is set to 999, but in your temp table, you set this to 101. You will never return a row into SRC when you use this field as a match critera. – websch01ar Oct 29 '18 at 16:44

1 Answers1

1

To elaborate on RBarry Young and Code Different's responses, NOT MATCHED compares what is in the SOURCE with what is in the target. Because I am selecting from the same table with the filter criteria, the source results are NULL so there is nothing to be NOT MATCHED. the code in the USING should look like this 

SELECT
    style = @style
    ,colour_id = @colourid
    ,size_id = @sizeid
    ,cost = @ctncost

This way the SOURCE will contain a results set with a single record which may or may not be found in the TARGET table. when it is not matched then the insert will be triggered.

Thanks for your help guys.

Aaron Reese
  • 131
  • 11