Edit Question - KnowledgeHub

Title Body

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, bu

Tags (comma-separated) Cancel