sql-server - SQL Server MERGE 语句和 ORDER BY 子句

Question

我想编写一个 MERGE 语句，通过使用 ORDER BY 子句从大表中选择 TOP 10 行并更新它的列值之一。 MERGE 语句允许我选择前 10 行，但我不能将 ORDER BY 子句放在任何地方。

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

您可以使用表表达式作为 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;

虽然使用起来可能更简单

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;