A Code Sample with Merge

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ddcefe1-4d16-4380-b146-ce262129d5f6/small-query-several-doubts?forum=transactsql#ded2a8a8-4ee5-4423-970b-b2a7d8b26662

 

 

  

 DECLARE @red TABLE ( ID INT, V INT, Payload INT );

INSERT  INTO @red
VALUES  ( 1, 1, 1 ),
        ( 2, 2, 2 ),
        ( 3, 1, 3 ),
        ( 4, 2, 4 ),
        ( 5, 1, 5 ),
        ( 6, 2, 6 ),
        ( 7, 1, 7 ),
        ( 8, 2, 8 ),
        ( 9, 1, 9 );

MERGE @red tgt
USING (  
SELECT	* 
		FROM	@red
		WHERE	[v] < (SELECT ISNULL(MAX([v]), -1) FROM  @red)
	) src ON tgt.id=src.id

WHEN NOT MATCHED BY TARGET THEN
	INSERT  (Payload) VALUES( src.Payload) 
WHEN NOT MATCHED BY SOURCE  THEN
 Update Set  ID=NULL,V=null
 ;
SELECT  * FROM    @red;

/*
ID	V	Payload
1	1	1
NULL	NULL	2
3	1	3
NULL	NULL	4
5	1	5
NULL	NULL	6
7	1	7
NULL	NULL	8
9	1	9

*/

--  MERGE @red tgt
--Using ( select * from  @red  WHERE  [v] >= (SELECT Isnull(Max([v]), -1) from  @red ) ) src on tgt.id=src.id

--When matched then
--Delete
--When not matched by target then
-- INSERT  (Payload) VALUES( src.Payload) ;

-- /*
-- ID	V	Payload
--1	1	1
--3	1	3
--5	1	5
--7	1	7
--9	1	9

--*/

-- select * from @Red

 

Edit:
A post with interesting MERGE samples:

Cool MERGE features you may not know about

---