Search code examples
sqlsql-servert-sqlmergedata-warehouse

MERGE - conditional "WHEN MATCHED THEN UPDATE"

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
Solution

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