SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table sets(
      SetId varchar(50),
      [Value] int
 );
INSERT INTO sets
SELECT 'Set1', 0
UNION ALL SELECT 'Set1', 1
UNION ALL SELECT ' Set2', 0
UNION ALL SELECT ' Set2', 1
4 rows affected

SELECT * FROM sets;


SetId
Value


Set1
0

Set1
1

 Set2
0

 Set2
1


SELECT COUNT(DISTINCT SetId) 
FROM (
MERGE sets trg
USING (SELECT 1 AS s) src ON 1=1
WHEN MATCHED THEN 
  DELETE
OUTPUT deleted.SetId
) sub
Msg 10729 Level 15 State 1 Line 3
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.