MERGE - conditional "WHEN MATCHED THEN UPDATE"

sion_corn picture sion_corn · Dec 18, 2012 · Viewed 29.6k times · Source

The highlights in the image below shows the logic I want to implement. I realize the syntax is incorrect.

Is there a way to conditionally update a record in a MERGE statement only if it the value of one of its columns in the target table is NULL, and the corresponding value in the source table is not null?

How would you suggest re-writing this?

MERGE dbo.input_311 AS [t]
USING dbo.input_311_staging AS [s]
ON ([t].[unique key] = [s].[unique key])
WHEN NOT MATCHED BY TARGET
    THEN INSERT(t.[Created Date]) VALUES(s.[Created Date])
WHEN MATCHED
    THEN UPDATE SET(t.[Created Date] = s.[Created Date]
                WHERE s.[Created Date] IS NOT NULL
                AND t.[Created Date] IS NULL)
OUTPUT deleted.*, $action, inserted.*;
GO

Answer

Laurence picture Laurence · Dec 18, 2012

You might be able to use When Matched And (s.[Created Date] Is Not Null And t.[Created Date] Is Null) Then Update ....