CODEWITHSUNDEEP
sqlsql-servermerge
soapergem
soapergem

Reputation: 10019

How do I limit the action of an OUTPUT clause in a MERGE statement?

Here's my query:

MERGE INTO [payments_orders] o USING (
    SELECT 1 AS [order_id], 50.00 AS [amount] UNION ALL 
    SELECT 2 AS [order_id], 50.00 AS [amount]
) AS t ([order_id], [amount]) 
ON o.[payment_id] = 4 AND o.[order_id] = t.[order_id] 
WHEN MATCHED THEN UPDATE SET o.[amount] = t.[amount] 
WHEN NOT MATCHED THEN INSERT ([payment_id], [order_id], [amount]) 
VALUES (4, t.[order_id], t.[amount]) 
WHEN NOT MATCHED BY SOURCE AND o.[payment_id] = 4 THEN DELETE 
OUTPUT $ACTION AS [action], [deleted].[order_id];

I'm running a MERGE statement to insert or update which order records are associated with which payment records and how that payment is allocated.

What I want to do is add WHERE $ACTION = 'DELETE' at the end; however that doesn't work. Apparently you can't use a WHERE clause within an OUTPUT clause; that's "incorrect syntax."

How do I make it so the only rows output from this statement are the ones triggered by the DELETE?

Upvotes: 2

Views: 1480

Answers (1)

alexwweston
alexwweston

Reputation: 86

I've run into the same problem. My solution so far (unless I come up with another one) is to insert dummy values into a temp table for the inserted rows, then clean up the table after the merge statement is done.

CREATE TABLE #deleted_orders (order_id int)

MERGE INTO [payments_orders] o USING (
    SELECT 1 AS [order_id], 50.00 AS [amount] UNION ALL 
    SELECT 2 AS [order_id], 50.00 AS [amount]
) AS t ([order_id], [amount]) 
ON o.[payment_id] = 4 AND o.[order_id] = t.[order_id] 
WHEN MATCHED THEN UPDATE SET o.[amount] = t.[amount] 
WHEN NOT MATCHED THEN INSERT ([payment_id], [order_id], [amount]) 
VALUES (4, t.[order_id], t.[amount]) 
WHEN NOT MATCHED BY SOURCE AND o.[payment_id] = 4 THEN DELETE 
OUTPUT ISNULL([deleted].[order_id], -1) INTO #deleted_orders


DELETE FROM #deleted_orders WHERE order_id=-1

Note: You'll need to check on performance, and decide if this change will cause an acceptable performance hit for your use case. Temporary tables can decrease performance: See the section on 'Temporary Tables' here for T-SQL

EDIT: I actually ended up doing something a little different, but fairly similar. It saves you the DELETE step at the end

INSERT INTO #deleted_orders (order_id)
        SELECT mrg.order_id
        FROM ( MERGE INTO [payments_orders] o USING (
                    SELECT 1 AS [order_id], 50.00 AS [amount] UNION ALL 
                    SELECT 2 AS [order_id], 50.00 AS [amount]
                ) AS t ([order_id], [amount]) 
                ON o.[payment_id] = 4 AND o.[order_id] = t.[order_id] 
                WHEN MATCHED THEN UPDATE SET o.[amount] = t.[amount] 
                WHEN NOT MATCHED THEN INSERT ([payment_id], [order_id], [amount]) 
                VALUES (4, t.[order_id], t.[amount]) 
                WHEN NOT MATCHED BY SOURCE AND o.[payment_id] = 4 THEN DELETE 
                OUTPUT [deleted].[order_id] INTO #deleted_orders
OUTPUT $action as MergeAction, deleted.order_id) AS mrg 
WHERE mrg.MergeAction = 'DELETE'

Upvotes: 4

Related Questions