Databases
StackOverflow2013
Postsdbo
POCan I apply a WHERE clause on the target in a MERGE statement?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I apply a WHERE clause on the target in a MERGE statement?
    primarykey
    data
    text
    <p>I have a target table containing a items that have an <code>IsActive</code> flag, and I am inserting and updating from a source table using a <code>MERGE</code> 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:</p> <ul> <li>if it exists in the source and the target the row should have <code>IsActive</code> true</li> <li>if it exists only in the source then a new row should be inserted to the target, with <code>IsActive</code> true</li> <li>if it exists only in the target then <code>IsActive</code> should be set to false.</li> </ul> <p>All very straightforward, except the target table also has a discriminating column <code>SourceId</code> which relates to the source table. So for a given source table, I only want to <code>MERGE</code> against rows with the corresponding <code>SourceId</code>.</p> <p>(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)</p> <p>Here's an example:</p> <pre><code>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 </code></pre> <p>My initial attempt was to include the <code>AND t.[SourceId] = @SourceId</code> 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.</p> <p>The end result is that whenever the procedure is run for a source system, all other systems will be set to inactive.</p> <p>My solution so far is to run the <code>MERGE</code> only for <code>MATCHED</code> and <code>NOT MATCHED BY TARGET</code>, and then run a subsequent <code>UPDATE</code> for the unmatched rows </p> <pre><code>UPDATE #target SET [IsEnabled] = 0 WHERE [SourceId] = @SourceId AND [ID] NOT IN (SELECT [ID] FROM #source) </code></pre> <p>Is there any way to include this filter condition in the <code>MERGE</code> statement? Are there any other clever ways to achieve this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKirk Broadhurst
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCan I apply a WHERE clause on the target in a MERGE statement?
      1. USKirk Broadhurst
      1. VTFavorite
    2. VO
      singulars
      1. POCan I apply a WHERE clause on the target in a MERGE statement?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCan I apply a WHERE clause on the target in a MERGE statement?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload