What is the quickest way to update each row in one table with a use-once value from another table?

I have a table called dbo.RecycleProductID that has just a primary key ProductID column and a Used column:

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

The table above has about 6.5m ProductID values that need to be 'recycled' and assigned to products in a different table. Once a ProductID has been assigned to a product, I must update the Used column value to 1 to indicate it has been used. There are gaps in the ProductID sequence, some in the thousands (.e.g it can jump from 1010 to 8055) and there are way more rows in this table than the receiving table dbo.Product table.

The dbo.Product table (has about 1.5m rows) is just a list of products but with no ProductID values.

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

I am currently running a while loop to get the ProductID values from dbo.RecycleProductID into the dbo.Product table:

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;

With this basically being a CURSOR, it is taking forever. It has been running for almost two days and only updated about 326515 rows. Is there a quicker way of doing this?

2 answers

  • answered 2018-01-20 11:09 uzi

    Try this query:

    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
    

    EDIT: This query will update RecycleProductID and can be executed seperately

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

  • answered 2018-01-20 11:09 Paparazzi

    Like the accepted solution
    For the second update try

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