By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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)
7 rows affected
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;
1 rows affected
SELECT * FROM #data
SELECT * FROM #anotherTable
SELECT * FROM #metadata
| id | description | metadata |
|---|---|---|
| 1 | data | 10 |
| id | description |
|---|---|
| 1 | example |
| id | description | metadata | state |
|---|---|---|---|
| 1 | data | 10 | 1 |
| 2 | more data | 11 | 0 |
| 3 | example | 12 | -1 |