sql-servert-sqlmergesql-delete

different deleting OUTPUT behaviour based on source table

What I'm trying to achieve is a combined merge statement, that updates/deletes/moves data based on related metadata, somewhat like the following (MWS at the end):

merge #data as target
using #metadata as source
on (...)

when matched and source.state = 1
then update ...

when matched and source.state = 0
then delete

when matched and source.state = -1
then delete output deleted... into *somewhere else*
;

This produces the following error:

An action of the WHEN MATCHED type may not occur more than once in a DELETE clause of a MERGE statement.

(translated)

Could someone elaborate on the error? How can I achieve the desired behaviour?

Here is a MWS:

CREATE TABLE #data 
(
    id int IDENTITY(1,1),
    description nvarchar(100) NOT NULL,
    metadata int NULL
)

CREATE TABLE #anotherTable 
(
    id int identity(1,1),
    description nvarchar(100) NOT NULL
)

INSERT INTO #data (description)
VALUES (N'data'),
       (N'more data'),
       (N'example'),
       (N'unknown')

CREATE TABLE #metadata 
(
    id int IDENTITY(1,1),
    description nvarchar(100) NOT NULL,
    metadata int NOT NULL,
    state int NOT NULL DEFAULT -1
)

INSERT INTO #metadata (description, metadata, state)
VALUES (N'data', 10, 1),
       (N'more data', 11, 0),
       (N'example', 12, -1)

MERGE #data AS target
USING #metadata AS source
      ON (target.description = source.description)

WHEN NOT MATCHED BY SOURCE THEN 
    DELETE

WHEN MATCHED AND source.state = 1 THEN
    UPDATE SET target.metadata = source.metadata

WHEN MATCHED AND source.state = 0 THEN
    DELETE

WHEN MATCHED AND source.state = -1 THEN
    DELETE OUTPUT deleted.description INTO #anotherTable (description);


SELECT * FROM #data
SELECT * FROM #anotherTable
SELECT * FROM #metadata

DROP TABLE #data, #metadata, #anotherTable

Appreciate your help!

Solution

  • No, you cannot do this directly. The OUTPUT clause, contrary to your confusing formatting, actually applies to the whole MERGE, and you should format it separate from the rest of the MERGE.

    You are also not allowed to have multiple WHEN MATCHED clauses, unless one has an AND filter and one does not (so no more than two). You'd need to remove the filter on state, or pre-filter the source.

    One option is to put the MERGE...OUTPUT in a derived table (not a CTE as that's not supported) then INSERT off that with a WHERE.

    INSERT #anotherTable (description)
SELECT description
FROM (
    MERGE #data AS target
    USING #metadata AS source
        ON (target.description = source.description)

    WHEN NOT MATCHED BY SOURCE THEN 
        DELETE

    WHEN MATCHED AND source.state = 1 THEN
        UPDATE SET target.metadata = source.metadata

    WHEN MATCHED THEN
        DELETE
  
    OUTPUT $action as action, deleted.*, source.state
) MergeOutput
WHERE action = 'DELETE'
  AND state = -1;

    db<>fiddle

    The above solution only works on simple WHERE filters, you cannot use joins etc.

    Another option is to just insert into a table variable then filter and re-insert from there.

    A couple of side notes: