Can I apply a WHERE clause on the target in a MERGE statement?

Question

I have a target table containing a items that have an IsActive flag, and I am inserting and updating from a source table using a MERGE statement. If something exists in the source table then it's active, and if it doesn't then it's not active. The logic is pretty simple:

• if it exists in the source and the target the row should have IsActive true
• if it exists only in the source then a new row should be inserted to the target, with IsActive true
• if it exists only in the target then IsActive should be set to false.

All very straightforward, except the target table also has a discriminating column SourceId which relates to the source table. So for a given source table, I only want to MERGE against rows with the corresponding SourceId.

(My normalised table contains rows of identical data types from multiple systems - I retrieve the data from those systems individually and thus the need to merge from one source at a time)

Here's an example:

IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target    
IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source

CREATE TABLE #target  ( Id INT, SourceId INT, IsActive BIT )   
INSERT #target VALUES (1, 1, 0)
INSERT #target VALUES (2, 1, 1)
INSERT #target VALUES (3, 2, 1)

CREATE TABLE #source ( Id INT )    
INSERT #source VALUES (1)
INSERT #source VALUES (4)

DECLARE @SourceId INT = 1;    
SELECT * FROM #target

MERGE INTO #target t
USING
(
    SELECT [Id] FROM #source
) AS s
ON t.[Id] = s.[Id] AND t.[SourceId] = @SourceId
WHEN MATCHED THEN UPDATE SET [IsActive] = 1
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES ([Id], @SourceId, 1)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET [IsActive] = 0;

SELECT * FROM #target

My initial attempt was to include the AND t.[SourceId] = @SourceId in the merge condition, but obviously that won't work - it's restricting the items to merge, but not the target table. The target row ID = 3 won't match, and so it will be set to inactive, whether or not that additional condition is included.

The end result is that whenever the procedure is run for a source system, all other systems will be set to inactive.

My solution so far is to run the MERGE only for MATCHED and NOT MATCHED BY TARGET, and then run a subsequent UPDATE for the unmatched rows

UPDATE #target
SET [IsEnabled] = 0 
WHERE [SourceId] = @SourceId
AND [ID] NOT IN (SELECT [ID] FROM #source)

Is there any way to include this filter condition in the MERGE statement? Are there any other clever ways to achieve this?

Answer 1

So your result set should be

1 1 1
2 1 0    
3 2 1
4 1 1

in which case your merge statement should be

merge #target as t
using #source as source
on (t.id=source.id)
when matched then update set isactive=1
when not matched by target then insert values (id, @sourceid,1)
when not matched by source and SourceID=@sourceID then update set isactive=0 

Full test:

CREATE TABLE #target  ( Id INT, SourceId INT, IsActive BIT )    
INSERT #target VALUES (1, 1, 0) 
INSERT #target VALUES (2, 1, 1) 
INSERT #target VALUES (3, 2, 1) 

CREATE TABLE #source ( Id INT )     
INSERT #source VALUES (1) 
INSERT #source VALUES (4) 

DECLARE @SourceId INT 
select @SourceId = 1;     

merge #target as t 
using #source as source 
on (t.id=source.id) 
when matched then update set isactive=1 
when not matched by target then insert values (id, @sourceid,1) 
when not matched by source and SourceID=@SourceID then update set isactive=0;


SELECT * FROM #target 

drop table #target;
drop table #source

results...

Id          SourceId    IsActive
----------- ----------- --------
1           1           1
2           1           0
3           2           1
4           1           1