SQL: MERGE : When not matched by SOURCE then UPDATE

kkran 831 Reputation points
2023-06-14T21:49:21.62+00:00

Hi Team - I have a MERGE SQL statement and i am facing an issue on how to update the target table column. Please see below, If the row doesn't exist in SOURCE ( I mean if the existing row got deleted ) then i want to update the TARGET column to GETDATE.

WHEN NOT MATCHED by SOURCE I wanted to update Target.DateRowDeleted = GETDATE().

I am looking for alternative options as well. Thank you

MERGE [CDW].[dbo].[TargetTable] TRG
USING [CDW].[dbo].[SourceTable_STAGE] SRC ON TRG.PO_RELEASE_ID=SRC.PO_RELEASE_ID
WHEN MATCHED THEN
UPDATE SET TRG.[LAST_UPDATE_DATE] = SRC.[LAST_UPDATE_DATE]
      ,TRG.[PO_HEADER_ID] = SRC.[PO_HEADER_ID]
      ,TRG.[RELEASE_NUMBER] = SRC.[RELEASE_NUMBER]
      ,TRG.[REVISION_NUMBER] = SRC.[REVISION_NUMBER]
    
 
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT ([PO_RELEASE_ID]
      ,[LAST_UPDATE_DATE]
      ,[PO_HEADER_ID]
      ,[RELEASE_NUMBER]
      ,[REVISION_NUMBER]
     )
VALUES (SRC.[PO_RELEASE_ID]
      ,SRC.[LAST_UPDATE_DATE]
      ,SRC.[PO_HEADER_ID]
      ,SRC.[RELEASE_NUMBER]
      ,SRC.[REVISION_NUMBER]
      )
 
WHEN NOT MATCHED BY SOURCE 
THEN 
UPDATE
SET TRG.DATEROWDELETED =  GETDATE();
SQL Server Other
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.