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.

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?

CodePudding user response：

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;

db<>fiddle