Perform an update to a value and use that value to update another value during the same update process

Running Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 19 2024 16:01:48 Copyright (C) 2022 Microsoft Corporation

I am trying to update a value in a column that is calculated using a value that was also updated during the same update process, but on an earlier record.

Think of this table as a rudimentary security table. The initial table columns are an Identity RowID, a unique PersonID, their name, the person they report to, and a field that lists the PersonIDs of those that can see their data.

DROP TABLE IF EXISTS ##People1

CREATE TABLE ##People1
( 
    RowID      INT PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
    PersonID   INT NULL, 
    PersonName VARCHAR(100) NULL, 
    ReportsTo  VARCHAR(100) NULL, 
    SeeMyData  VARCHAR(MAX) NULL
)
GO

INSERT INTO ##People1 (PersonID, PersonName, ReportsTo)
VALUES (7036, 'Liesl', NULL),
       (4049, 'Friedrich', 7036),
       (197, 'Louisa', 4049),
       (2303, 'Kurt', 197),
       (3409, 'Brigitta', 2303),
       (5686, 'Marta', 4049),
       (533, 'Gretl', 5686),
       (5204, 'Mike', 533),
       (4063, 'Sara', 3409),
       (1928, 'Tom', 197),
       (7013, 'Jerry', 1928),
       (7033, 'Sue', 533)
GO

In the table above, the second person, Friedrich, his personid is 4049, and he reports to 7036 Liesl. Liesl doesn't report to anyone (the top of the chain). The person who someone reports to can be anyone that has a personID that is listed before them.

For example, PersonID 1928, Tom, reports to Louisa, personID 197. The person someone reportsto will always be in an earlier row.

Notice there are no values for the field SeeMyData. The goal is to calculate and update the string value for SeeMyData for each personID, based on this string equation in pseudocode:

SeeMyData = CONCAT(The SeeMyData string of the person they report to,'-', the PeopleID of the person they report to)

The results I am looking for:

And so on.

So the challenge for me is how to calculate the SeeMyData value that's using a previously calculated value in the same column. In other words, update the table with each new calculated value before trying to calculate the SeeMyData value for the next row.

I tried to copy the table and use a piece of code I found in my searches:

DROP TABLE IF EXISTS ##People2

SELECT * 
INTO ##People2 
FROM ##People1
GO

UPDATE ##People1
SET ##People1.seemydata = CONCAT(p2.SeeMyData, '-', p1.ReportsTo) 
FROM ##People1 p1
JOIN ##People2 p2 ON p1.reportsto = p2.PersonID

The problem with this code is the same issue I have with other solutions, the actual value of SeeMyData doesn't seem to be written to the column after calculation, so the next row still sees a NULL in the SeeMyData column.

I have also tried to use the LAG function and calculating the difference between the RowIDs as the Ordinal, but I have the same problem where the update values for the SeeMyData column aren't really there, and we won't know what the RowID is since it is an Identity Row.

I apologize for any formatting problems, I thought that it would format the code blocks but it didn't seem to work for me.

DROP TABLE IF EXISTS ##People1;
CREATE TABLE ##People1
(
    RowID       INT  PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
    PersonID    INT NULL, 
    PersonName VARCHAR(100) NULL, 
    ReportsTo  INT NULL,  -- Changed to INT to match PersonID type
    SeeMyData  VARCHAR(MAX) NULL
);
GO

INSERT INTO ##People1
(PersonID, PersonName, ReportsTo)
VALUES
(7036, 'Liesl',NULL),
(4049, 'Friedrich',7036),
(197,'Louisa',4049),
(2303,'Kurt',197),
(3409,'Brigitta',2303),
(5686,'Marta',4049),
(533,'Gretl',5686),
(5204,'Mike',533),
(4063,'Sara',3409),
(1928,'Tom',197),
(7013,'Jerry',1928),
(7033,'Sue',533);
GO

WITH HierarchyCTE AS
(
    SELECT 
        RowID,
        PersonID,
        PersonName,
        ReportsTo,
        CAST(NULL AS VARCHAR(MAX)) AS SeeMyData,
        CAST(CAST(PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
    FROM 
        ##People1
    WHERE 
        ReportsTo IS NULL

    UNION ALL

    SELECT 
        p.RowID,
        p.PersonID,
        p.PersonName,
        p.ReportsTo,
        CAST(NULL AS VARCHAR(MAX)) AS SeeMyData,
        CAST(h.Path + '-' + CAST(p.PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
    FROM 
        ##People1 p
    INNER JOIN 
        HierarchyCTE h ON p.ReportsTo = h.PersonID
)

UPDATE p
SET SeeMyData = CASE WHEN p.ReportsTo IS NULL THEN NULL ELSE SUBSTRING(h.Path, 1, LEN(h.Path) - LEN(CAST(p.PersonID AS VARCHAR(MAX))) - 1) END
FROM ##People1 p
INNER JOIN HierarchyCTE h ON p.RowID = h.RowID;


SELECT 
    *
FROM 
    ##People1
ORDER BY 
    RowID;
GO

You can run the sql here: SqlOnline

OR

DROP TABLE IF EXISTS ##People1;
CREATE TABLE ##People1
(
    RowID       INT PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
    PersonID    INT NULL, 
    PersonName VARCHAR(100) NULL, 
    ReportsTo  INT NULL,
    SeeMyData  VARCHAR(MAX) NULL
);
GO

INSERT INTO ##People1
(PersonID, PersonName, ReportsTo)
VALUES
(7036, 'Liesl',NULL),
(4049, 'Friedrich',7036),
(197,'Louisa',4049),
(2303,'Kurt',197),
(3409,'Brigitta',2303),
(5686,'Marta',4049),
(533,'Gretl',5686),
(5204,'Mike',533),
(4063,'Sara',3409),
(1928,'Tom',197),
(7013,'Jerry',1928),
(7033,'Sue',533);
GO


DROP TABLE IF EXISTS #Hierarchy;

CREATE TABLE #Hierarchy
(
    RowID INT PRIMARY KEY,
    PersonID INT,
    PersonName VARCHAR(100),
    ReportsTo INT,
    SeeMyData VARCHAR(MAX),
    Path VARCHAR(MAX)
);


INSERT INTO #Hierarchy (RowID, PersonID, PersonName, ReportsTo, SeeMyData, Path)
SELECT 
    RowID,
    PersonID,
    PersonName,
    ReportsTo,
    NULL AS SeeMyData,
    CAST(PersonID AS VARCHAR(MAX)) AS Path
FROM 
    ##People1
WHERE 
    ReportsTo IS NULL;

-- Iteratively insert people who report to those already in the temporary table
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    INSERT INTO #Hierarchy (RowID, PersonID, PersonName, ReportsTo, SeeMyData, Path)
    SELECT 
        p.RowID,
        p.PersonID,
        p.PersonName,
        p.ReportsTo,
        NULL AS SeeMyData,
        CAST(h.Path + '-' + CAST(p.PersonID AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Path
    FROM 
        ##People1 p
    INNER JOIN 
        #Hierarchy h ON p.ReportsTo = h.PersonID
    WHERE 
        NOT EXISTS (SELECT 1 FROM #Hierarchy WHERE RowID = p.RowID);

    SET @RowCount = @@ROWCOUNT;
END;

-- Update SeeMyData column
UPDATE p
SET SeeMyData = CASE WHEN p.ReportsTo IS NULL THEN NULL ELSE SUBSTRING(h.Path, 1, LEN(h.Path) - LEN(CAST(p.PersonID AS VARCHAR(MAX))) - 1) END
FROM ##People1 p
INNER JOIN #Hierarchy h ON p.RowID = h.RowID;

--output
SELECT 
    *
FROM 
    ##People1
ORDER BY 
    RowID;
GO