It is difficult to know where to start with this one.
Firstly you should provide consumable test data. Going by your description the primary key should really be Product_ID but it has unknown values so it cannot be the PK. There is no PK in your data so I will just create a heap. (In relational theory a table needs a PK.)
CREATE TABLE #Products
(
Product_Name varchar(30) NOT NULL
,Product_ID int NULL
)
INSERT INTO #Products
VALUES ('Jacket', 10)
,('Sofa', 20)
,('Leather Wallet', 30)
,('Lawn Mower', NULL)
,('Sofa', NULL)
,('Gym Kit', NULL);
Secondly a relation (table) is an unordered set so in theory I can see no logical way to assign:
Lawn Mower 40
Sofa 50
Gym Kit 60
I can assign them as:
Gym Kit 40
Lawn Mower 50
Sofa 60
ordering by Product_Name.
WITH MaxID
AS
(
SELECT MAX(Product_ID) AS Product_ID
FROM #Products
WHERE Product_ID IS NOT NULL
)
,NewIDs
AS
(
SELECT P.Product_Name, P.Product_ID
,M.Product_ID
+ ROW_NUMBER() OVER (ORDER BY Product_Name) * 10 AS New_ID
FROM #Products P
CROSS JOIN MaxID M
WHERE P.Product_ID IS NULL
)
UPDATE NewIDs
SET Product_ID = New_ID;
select * from #Products;
Given the way SQL server stores data it is quite likely, but not guaranteed, that especially with small tables under low load that the heap will be processed in the order the data was entered if no order is given. This is totally non relational as is NOT guaranteed!!!!!
-- reset data
UPDATE #Products
SET Product_ID = NULL
WHERE Product_ID > 30;
select * from #Products;
WITH MaxID
AS
(
SELECT MAX(Product_ID) AS Product_ID
FROM #Products
WHERE Product_ID IS NOT NULL
)
,NewIDs
AS
(
SELECT P.Product_Name, P.Product_ID
,M.Product_ID
+ ROW_NUMBER() OVER (ORDER BY (SELECT NULL) ) * 10 AS New_ID
FROM #Products P
CROSS JOIN MaxID M
WHERE P.Product_ID IS NULL
)
UPDATE NewIDs
SET Product_ID = New_ID;
select * from #Products;