This is a small selection from my product table. I need to add the PLU_code value to products that have PLU_code 0, but in such a way that the new value fills an empty gap in the order. For example, Product 8 will have a PLU_code of 2, product 9 - 4, product 10 - 5, etc.
| ID | SKU_code | name | PLU_code |
|---|---|---|---|
| 11584 | AB1234 | product 1 | 1 |
| 11659 | AB4321 | product 2 | 3 |
| 11660 | ED-7044 | product 3 | 9 |
| 11661 | Z2854 | product 4 | 10 |
| 11825 | 7703311 | product 5 | 29 |
| 11826 | 7703852 | product 6 | 146 |
| 11882 | 7707698 | product 7 | 147 |
| 11965 | 992431 | product 8 | 0 |
| 11966 | 992432 | product 9 | 0 |
| 11999 | CB35208 | product 10 | 0 |
I found a similar problem in this forum, but it was solved in a way that I don't like. I have a condition that the product table cannot be deleted and recreated. I can only update. Those PLU_code values that already exist must be preserved.
Can anyone help me with a solution?
Perhaps you could use a Tally to generate the values you need, and then use an UPDATEable CTE to UPDATE the rows which have a value of 0 for PLU_CODE.
CREATE OR ALTER FUNCTION [fn].[Tally] (@End bigint, @StartAtOne bit)
RETURNS table
AS RETURN
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
WHERE @StartAtOne = 0
UNION ALL
SELECT TOP (@End)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
SELECT I
FROM Tally;
GO
DECLARE @Max int = (SELECT COUNT(*) FROM dbo.YourTable); --This might be better done using the sys objects though
WITH Tally AS(
SELECT T.I,
ROW_NUMBER() OVER (ORDER BY T.I) AS RN
FROM fn.Tally(@Max, 1) T
WHERE NOT EXISTS (SELECT 1
FROM dbo.YourTable YT
WHERE T.I = YT.PLU_Code)),
RNs AS(
SELECT PLU_Code,
ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
FROM dbo.YourTable YT
WHERE YT.PLU_Code = 0)
UPDATE RNs
SET PLU_Code = T.I
FROM RNs
JOIN Tally T ON RNs.RN = T.RN;