14,494 questions
SQL: MERGE : When not matched by SOURCE then UPDATE
kkran
831
Reputation points
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
Sign in to answer