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!

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!

• sql-server
• t-sql
• merge
• sql-delete

• 2 I don't get it, you have two deletes, why? You cannot output values for one action only, all the actions are automatically outputted, so remove the duplicate delete and then handle your logic later – siggemannen Commented Nov 19, 2024 at 12:11
• So you want to use a MERGE, eh? Honestly, you are likely better off with INSERT/UPDATE/DELETE statements, with appropriate lock hints, inside a transaction. – Thom A Commented Nov 19, 2024 at 12:16
• @siggemannen the idea is that based on the metadata some of the rows should be deleted and some of them should be moved. Is there a better solution than using delete output? But I now understand at least the underlying issue, thanks! – Johnny Commented Nov 19, 2024 at 15:18

1 Answer 1

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:

• You should really use a HOLDLOCK hint on MERGE if you are using production tables rather than temp tables.
• Be careful with WHEN NOT MATCHED BY SOURCE, because you need to ensure that the ON clause only specifies the matching criteria, not any extra filters, or you will end up deleting more than intended.
  If you need to merge only a subset then pre-filter the target with a CTE.