代码之家 › 专栏 › 技术社区 › volume one

使用另一个表中的一次使用值更新一个表中的每一行的最快方法是什么?

sql-server-2017 sql-server-2012 tsql sql-server sql

volume one · 技术社区 · 7 年前

我有一张桌子叫 dbo.RecycleProductID 只有主键 ProductID 列和a Used 列:

ProductID (pk, int) | Used (bit)
23                    1   
65                    1
68                    1
90                    NULL
104                   NULL  
...
60983471              NULL

上表约6.5m 产品ID 需要“回收”并分配给不同表中产品的值。每年一次 产品ID 已分配给产品,我必须更新 习惯于 列值到 1 表示已使用。在 产品ID 顺序,有些是数千行(例如,它可以从1010跳到8055),并且此表中的行数远远多于接收表中的行数 dbo.Product 桌子

这个 dbo。产品 表(大约有150万行)只是一个产品列表,但没有 产品ID 价值观

ProductID (pk, int)  | ProductName (varchar)
23                     Toothpaste
65                     Speakers
68                     Galaxy S8
NULL                   Plate
NULL                   Monitor
NULL                   Carpet
.....

我当前正在运行while循环以获取 产品ID 值来自 dbo。回收产品ID 进入 dbo。产品 表格:

DECLARE @Max int = (select max(ProductID) from [dbo].[RecycleProductID]);
DECLARE @Min int = (select min(ProductID) from [dbo].[RecycleProductID]);

while @Min <= @Max 
begin

UPDATE TOP (1)
[dbo].[Product]
SET ProductID = (SELECT TOP 1 ProductID FROM [dbo].[RecycleProductID] b1 WHERE b1.Used IS NULL ORDER BY ProductID ASC)
OUTPUT INSERTED.ProductID INTO dbo.UsedProductID
WHERE 
ProductID is null;

UPDATE  
    rp1
SET    
    rp1.Used = 1
FROM
    [dbo].[RecycleProductID] rp1
INNER JOIN
    dbo.UsedProductID ub1 ON 
    ub1.ProductID = rp1.ProductID

set @Min = @Min+1
end;

由于这基本上是一个光标,它需要花费很长时间。它已经运行了将近两天,只更新了大约326515行。有没有更快的方法?

3 回复 | 直到 7 年前

uzi 7 年前

尝试此查询:

with t1 as (
    select
        ProducID, row_number() over (order by ProducID) rn
    from 
        RecycleProductID
    where
        used is null
)
, t2 as (
    select
        ProducID, row_number() over (order by ProducID) rn
    from 
        Product
    where
        ProductID is null
)

update t2
set t2.ProducID = t1.ProducID
from
    t2
    join t1 on t2.rn = t1.rn

编辑:此查询将更新RecycleProductID,并且可以单独执行

update RecycleProductID
set used = 1
where ProducID in (select ProductID from Product)

NDedob 3 年前

由于MYSQL不允许使用更新,您可以使用此查询在MYSQL上执行此操作:

with t1 as (
    select
        ProducID, row_number() over (order by ProducID) rn
    from 
        RecycleProductID
    where
        used is null
)
, t2 as (
    select
        ID, ProducID, row_number() over (order by ProducID) rn
    from 
        Product
    where
        ProductID is null
)

update Product t3
join t2 on t2.ID = t3.ID
join t1 on t1.rn = t2.rn

set t3.ProducID = t1.ProducID;

paparazzo 7 年前

喜欢公认的解决方案
对于第二次更新,请尝试

update RecycleProductID
set used = 1 
from RecycleProductID
join Product
  on RecycleProductID.ProductID = Product.ProductID  
 and used is null