locked
Merge SQL Error RRS feed

  • Question

  • User-322746205 posted

    Hi all,
    I have follow SQL Merge query. If found then update else will perform insert. But its not achieving what I expect. Although row is matched, SQL will still insert new row instead of update. Anyone have any idea on this?

    MERGE 
        INTO dbo.myTable AS TARGET
        USING dbo.myData AS SOURCE
        ON TARGET.tableID = SOURCE.tableID 
        and TARGET.batchNumber = SOURCE.batchNumber AND TARGET.custID = SOURCE.custID
        AND TARGET.companyName = SOURCE.companyName AND TARGET.Phone = SOURCE.Phone 
    
    
        WHEN MATCHED
        THEN   UPDATE SET
        TARGET.tableID =SOURCE.tableID ,
    	TARGET. custID=SOURCE.custID,
    	TARGET.companyName=SOURCE.companyName,
    	TARGET.Phone=SOURCE.Phone,
    	Target.batchNumber =Source.batchlNumber,
    	TARGET.createdBy = 'Admin',
    	TARGET.createdOn=GETDATE()
    	
    	WHEN NOT MATCHED BY TARGET
    	THEN INSERT (tableID ,batchNumber,custID,companyName,Phone,createdOn,createdBy)
    	VALUES (SOURCE.tableID ,SOURCE.batchNumber,SOURCE.custID,SOURCE.companyName,SOURCE.Phone,GETDATE(),'Admin')
    	;
    	
    	

     

    Monday, December 12, 2016 2:49 PM

Answers

All replies

  • User753101303 posted

    Hi,

    I would not expect that you want to match on 5 fields and then update those same 5 fields  and then just additionaly update createdBy and createdOn ? And updating the fields on which you match doesn't make sense anyway.

    Double check but for now it make me think that your match clause is perhaps just not what you actually want ??

    Monday, December 12, 2016 3:01 PM
  • User-322746205 posted

    Hi Patrice,

    When its matched, it suppose to do nothing. Although I comment the entire update query, its still not achieving what I expect.
    Please help.

    Monday, December 12, 2016 3:20 PM
  • User753101303 posted

    Do you have nullable columns maybe and it doesn't work as you expect when one of those columns is NULL ? If yes see for example https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b846f947-35f5-47fa-b58d-96fa698ca9bd/how-to-update-previously-null-columns-using-merge-statement?forum=transactsql (this is the usual behavior you have even for "regular" joins).

    Else what if yu try to write a SELECT statement that shows the rows you expect to insert  rather than update. It might help to understand what you see vs what you expect (hopefully thr issue is on NULL values)...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 12, 2016 5:07 PM
  • User77042963 posted

    Run this query you will find whether you have any matched rows.

    select * from dbo.myTable AS TARGET
      JOIN dbo.myData AS SOURCE
        ON TARGET.tableID = SOURCE.tableID 
        and TARGET.batchNumber = SOURCE.batchNumber AND TARGET.custID = SOURCE.custID
        AND TARGET.companyName = SOURCE.companyName AND TARGET.Phone = SOURCE.Phone 

    Tuesday, December 13, 2016 2:15 PM