Issue with delete statement in merge statement-CodePudding

I am working with merge into statement

I have a table that looks like this (first I insert like this) using below query:


5	2
5	3
5	5
5	6

table type :

CREATE TYPE [dbo].[userid] AS TABLE(
    [userid] [bigint] NULL
)
GO

Now I want the below output :


5	2
5	3
5	6

I write the below query like this:

--use test
declare @sid varchar(100) = '5'
declare @uid as userid

insert into @uid(userid) values(2)
insert into @uid(userid) values(3)
--insert into @uid(userid) values(5)    // I remove this line 
insert into @uid(userid) values(6)

MERGE INTO dbo.test_master AS dest
USING @uid AS sou ON
    dest.sid = @sid
    AND
    sou.userid = dest.testid
WHEN MATCHED THEN UPDATE SET
    dest.testid = sou.userid
WHEN NOT MATCHED THEN
    INSERT( sid, testid )
    VALUES( @sid, sou.userid )
--WHEN NOT MATCHED BY SOURCE
--    THEN
--        DELETE
;

I am trying to achieve this output


5	2
5	3
5	6

I am using delete keyword, see my SQL query, but it is deleting the all records from the table. I try, but can't work it out.

CodePudding user response：

You need to pre-filter the destination table, otherwise all rows, even ones that have a different sid will be deleted. You can pre-filter with a CTE or a view.

I note that the WHEN MATCHED clause makes no sense, as the only column being updated is the join column, which obviously matches anyway.

declare @sid varchar(100) = '5';
declare @uid as userid;

insert into @uid (userid) values
  (2),
  (3),
--  (5),    -- I remove this line 
  (6);

WITH dest AS (
    SELECT *
    FROM dbo.test_master dest
    WHERE dest.sid = @sid
)
MERGE INTO dest
USING @uid AS sou ON
    sou.userid = dest.testid
-- WHEN MATCHED THEN UPDATE SET
--    dest.testid = sou.userid   -- doesn't make sense
WHEN NOT MATCHED THEN
    INSERT( sid, testid )
    VALUES( @sid, sou.userid )
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE
;

db<>fiddle