SQL Server MERGE statement and ORDER BY clause

Question

I would like to write a MERGE statement to pick TOP 10 rows from a large table by using ORDER BY clause and update it’s one of the column values. MERGE statement allows me to pick TOP 10 rows but I could not put ORDER BY clause anywhere.

MERGE TOP(10) StudentAllocation AS SA
USING (SELECT @sub_id AS subId) AS TSA ON SA.sub_id = TSA.subId
WHEN MATCHED THEN 
       UPDATE SET SA.exam_batch = 1);

Answer 1

You can use a table expression as both the source and target for the MERGE.

WITH SA AS
(
SELECT TOP(10) sub_id,
               exam_batch 
FROM StudentAllocation 
ORDER BY sub_id
)
MERGE SA
USING (SELECT @sub_id AS subId) AS TSA ON SA.sub_id = TSA.subId
WHEN MATCHED THEN 
       UPDATE SET SA.exam_batch = 1;

although it might be simpler to use

WITH SA AS
(
SELECT TOP(10) sub_id,
               exam_batch 
FROM StudentAllocation 
ORDER BY sub_id
)
UPDATE SA
SET exam_batch = 1
WHERE sub_id = @sub_id;