admin管理员组

文章数量:1026989

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
Share Improve this question edited Nov 19, 2024 at 14:47 Charlieface 73.6k8 gold badges33 silver badges64 bronze badges asked Nov 19, 2024 at 12:08 JohnnyJohnny 455 bronze badges 3
  • 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
Add a comment  | 

1 Answer 1

Reset to default 2

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.

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
Share Improve this question edited Nov 19, 2024 at 14:47 Charlieface 73.6k8 gold badges33 silver badges64 bronze badges asked Nov 19, 2024 at 12:08 JohnnyJohnny 455 bronze badges 3
  • 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
Add a comment  | 

1 Answer 1

Reset to default 2

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.

本文标签： sql serverdifferent deleting OUTPUT behaviour based on source tableStack Overflow

版权声明：本文标题：sql server - different deleting OUTPUT behaviour based on source table - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745563056a2156278.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。