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