有效更新980K条记录，其中行在组中具有最大值

Question

我很难在合理的时间内更新数据库中的980000条记录。

在PriceID等于组(按ProductID分组)的最大值的行中，我必须将NextLp的值设置为"NULL"。

为了可视化，这里是我的例子：

+-----------+---------+--------+---------------------+|ProductID|PriceID|NextLp|是否将NextLp设置为NULL|+-----------+---------+--------+---------------------+|苹果|1|-||+-----------+---------+--------+---------------------+|苹果|2|-||+-----------+---------+--------+---------------------+|APPLE |3|-|是|+-----------+---------+--------+---------------------+|梨|1|-||+-----------+---------+--------+---------------------+|PEAR|2|-|是|+-----------+---------+--------+---------------------+

我尝试了各种方法，但所有这些方法都至少花了15秒更新了100条记录。。还有979100个，所以我的方法肯定不可行。

我感谢你的帮助。

版本为MS SQL 2016

Answer 1

也许是这样？

DECLARE @tbl TABLE(ProductID VARCHAR(100),PriceID INT,NextLp VARCHAR(100));
INSERT INTO @tbl VALUES
('APPLE',1,'-')
,('APPLE',2,'-')
,('APPLE',3,'-')
,('PEAR ',1,'-')
,('PEAR ',2,'-');
WITH UpdatableCTE AS
(
SELECT * 
,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY PriceID DESC) AS Nr
FROM @tbl 
)
UPDATE UpdatableCTE SET NextLp=NULL
WHERE Nr=1;
SELECT * FROM @tbl;

您可以首先使用可更新的CTE将数字放置到您的行中，然后更新您的目标行。

更新

结果

ProductID   PriceID NextLp
APPLE       1       -
APPLE       2       -
APPLE       3       NULL
PEAR        1       -
PEAR        2       NULL

在上面的示例中，有两行被设置为NULL。这两行都是其组中最高的PriceID。。。

ROW_NUMBER()是窗口函数。OVER()-子句允许PARTITION BY在集合中创建组，而ORDER BY将对组进行排序，在本例中按PriceID降序排列。这将把一个1绑定到每一行，其中PriceID是一组ProductID中最高的。

提示：您可以使用SELECT * FROM UpdatableCTE而不是UPDATE语句。这将显示编号的行，并允许检查编号。