SQLTeam.com | Weblogs | Forums

Help on sql query

Hi,

I am practicing my sql skills , Struggling to get solutions for below

Product table has

Product_Name Product_ID
Jacket 10
Sofa 20
Leather Wallet 30
Lawn Mower ?
Sofa ?
Gym Kit ?

Every Product ID keep increase by 10.
Any query to update null product id values as.
Lawn Mower 40
Sofa 50
Gym Kit 60

Please share update query to handle these scenario.

Thanks

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;