MERGE DELETE only based on parent id

Question

i have a table called Availability that looks like this.

| Id | Allocated | AverageCost | Demand | InStock | SourceDate | LocationId | ItemId

now ItemId is a relationship to Items table and LocationId is a relationship to Location table, there can only be 1 record per location for each item.

i have another table that is having updated Availability but only for some items, not all items, now i want to make a merge and insert all which is missing from the target, and update the target also, but my challenge is what do i do when let say some locations from specific items have been removed, i want to delete when source is missing the Availability ONLY for the items that i am now matching,

here is an example. let say in the source i have multiple records for ItemId 2356, now i want to match them with target only for the records that have itemId = 2356. so when i have in target 6 records for ItemId 2356 and in source i only have 4 records for this ItemId, i want it should delete the 2 records from target that are missing from Source.

Target

ID  A   AC     D   IS    Date      LocationId    ItemID
1 | 0 | 2.36 | 23 | 56 | 3/23/18 | 5689       | 2356 
2 | 0 | 5.36 | 10 | 34 | 3/23/18 | 5634       | 2356 
3 | 0 | 5.36 | 10 | 34 | 3/23/18 | 5756       | 1497
4 | 0 | 5.36 | 10 | 34 | 3/23/18 | 5371       | 2356 
5 | 0 | 5.36 | 10 | 34 | 3/23/18 | 2873       | 2356
6 | 0 | 5.36 | 10 | 34 | 3/23/18 | 8549       | 2356
7 | 0 | 5.36 | 10 | 34 | 3/23/18 | 8549       | 3585
8 | 0 | 5.36 | 10 | 34 | 3/23/18 | 8549       | 2943
9 | 0 | 5.36 | 10 | 34 | 3/23/18 | 2958       | 2356

Source

 A   AC     D   IS    Date      LocationId    ItemID
 0 | 2.36 | 23 | 56 | 3/23/18 | 5689       | 2356 
 0 | 5.36 | 10 | 34 | 3/23/18 | 5634       | 2356 
 0 | 5.36 | 10 | 34 | 3/23/18 | 2873       | 2356
 0 | 5.36 | 10 | 34 | 3/23/18 | 8549       | 2356

but unfortunately my current MERGE is deleting everything from target that it doesnt find in source, including records that have a different ItemId.

here is my MERGE.

MERGE Availability AS target
USING #tmpAvailability AS SOURCE 
ON target.[locationId] = SOURCE.[locationId]
  AND target.[ItemId] = Source.[ItemId]   


WHEN MATCHED THEN 

UPDATE SET TARGET.[Allocated] = source.[Allocated],  
           TARGET.[AverageCost] =  source.[AverageCost],
           TARGET.[Demand] =  source.[Demand],
           TARGET.[InStock] =  source.[InStock],
           TARGET.[SourceDate] =  source.[SourceDate]


WHEN NOT MATCHED BY TARGET THEN  

INSERT ([Allocated],[AverageCost],[Demand],[InStock],[SourceDate],[LocationId],[ItemId])          
VALUES (SOURCE.[Allocated] ,SOURCE.[AverageCost],source.[Demand],source.[InStock],source.[SourceDate],source.[LocationId],source.[ItemId])


when Not Matched By Source and source.[ItemId] is not null then
DELETE; 

Answer 1

Please try this:

;WITH MergeTable AS (
    SELECT a.[Allocated],a.[AverageCost],a.[Demand],a.[InStock],a.[SourceDate],a.[LocationId],a.[ItemId]
    FROM Availability a
    WHERE a.ItemID IN (SELECT DISTINCT ItemID FROM #tmpAvailability)
)
MERGE MergeTable AS target
USING #tmpAvailability AS SOURCE 
    ON target.[locationId] = SOURCE.[locationId]
        AND target.[ItemId] = Source.[ItemId]   
WHEN MATCHED THEN 
UPDATE SET TARGET.[Allocated] = source.[Allocated],  
           TARGET.[AverageCost] =  source.[AverageCost],
           TARGET.[Demand] =  source.[Demand],
           TARGET.[InStock] =  source.[InStock],
           TARGET.[SourceDate] =  source.[SourceDate]

WHEN NOT MATCHED BY TARGET THEN  
INSERT ([Allocated],[AverageCost],[Demand],[InStock],[SourceDate],[LocationId],[ItemId])          
VALUES (SOURCE.[Allocated] ,SOURCE.[AverageCost],source.[Demand],source.[InStock],source.[SourceDate],source.[LocationId],source.[ItemId])

WHEN NOT MATCHED BY SOURCE AND source.[ItemId] IS NOT NULL THEN
DELETE;