5

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);
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
Krishnaraj Barvathaya
  • 595
  • 3
  • 9
  • 20

1 Answers1

8

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;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • In this specific scenario, your solution is correct. One thing to keep in mind though is that the `TOP` clause specified on a `MERGE` is performed *after* any rows are filtered out by additional `WHERE` clauses in the `WHEN MATCHED\NOT MATCHED` statements. If the user had any additional filters in his `WHEN MATCHED` clause, `MERGE TOP(X)` would always affect 10 rows (unless there are less than 10 possible rows to update). Your solution would only affect 10 rows, minus any that were filtered out in the `WHEN MATCHED` clause. – Jon Senchyna Jan 31 '14 at 14:01