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

Question

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?

Answer 1

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)

Answer 2

Because MYSQL don't allow update on WITH, you can use this query to do the job on 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;

Answer 3

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