SQL Server MERGE 语句和 ORDER BY 子句 [英] SQL Server MERGE statement and ORDER BY clause

问题描述

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

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);

推荐答案

您可以使用表表达式作为 MERGE 的源和目标.

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;

这篇关于SQL Server MERGE 语句和 ORDER BY 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！