From last id, recursively find parent id and fill column

Question

CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
) 

INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('A3DEBD5A-C8DD-494E-B2CC-97D693677071','70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('CBC648CE-B98C-4831-B6E2-FBF437BD012B','A3DEBD5A-C8DD-494E-B2CC-97D693677071','CA4462D3-BD2C-4C80-A50E-586DA28A877A');

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table.

What I want to do is:

1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with its occurrence in COL1 (step2)
2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the column 'Expected Result' for each row concerned by this ID.

I have tried some self-joins on the table but I'm very far from the expected result. Any idea of how this can be achieved?

Answer 1

If you have multiple root nodes then you can use a modified version of @PaulWhite's solution, re-joining it back to the main table to do the update.

WITH R AS
    (
        -- Anchor
        SELECT
            T1.COL1,
            Id = T1.COL1,
            Parent = T1.COL2
        FROM dbo.T1 AS T1

        UNION ALL 

        -- Recursive
        SELECT
            R.COL1,
            R.Parent,
            T1.COL2
        FROM R
        JOIN dbo.T1 AS T1
            ON R.Parent = T1.COL1
        WHERE
            R.Id <> R.Parent
    )
-- The result is in the row with the
-- highest level of recursion
UPDATE T1
SET Result = R.Parent
FROM R
JOIN dbo.T1
    ON T1.COL1 = R.COL1
WHERE
    R.Parent = R.Id
OPTION (MAXRECURSION 100);

db<>fiddle

Answer 2

A solution using a recursive common table expression:

CREATE TABLE dbo.T1
(
    COL1 uniqueidentifier NOT NULL PRIMARY KEY,
    COL2 uniqueidentifier NOT NULL,
    Result uniqueidentifier NULL
);

INSERT dbo.T1 
    (COL1, COL2)
VALUES
    ({guid 'CA4462D3-BD2C-4C80-A50E-586DA28A877A'}, {guid 'CA4462D3-BD2C-4C80-A50E-586DA28A877A'}),
    ({guid '70CA5891-F6D0-40BA-A6FC-2DA8A035F983'}, {guid 'CA4462D3-BD2C-4C80-A50E-586DA28A877A'}),
    ({guid 'A3DEBD5A-C8DD-494E-B2CC-97D693677071'}, {guid '70CA5891-F6D0-40BA-A6FC-2DA8A035F983'}),
    ({guid 'CBC648CE-B98C-4831-B6E2-FBF437BD012B'}, {guid 'A3DEBD5A-C8DD-494E-B2CC-97D693677071'});

DECLARE 
    @Result uniqueidentifier,
    @Start uniqueidentifier = {guid 'A3DEBD5A-C8DD-494E-B2CC-97D693677071'};

WITH 
    R AS
    (
        -- Anchor
        SELECT 
            T1.COL2,
            lvl = 1
        FROM dbo.T1 AS T1
        WHERE 
            T1.COL1 = @Start

        UNION ALL 

        -- Recursive
        SELECT 
            T1.COL2,
            R.lvl + 1
        FROM R
        JOIN dbo.T1 AS T1
            ON R.COL2 = T1.COL1
        WHERE
            T1.COL2 <> T1.COL1
    )
-- The result is in the row with the
-- highest level of recursion
SELECT TOP (1)
    @Result = R.COL2
FROM R
ORDER BY 
    R.lvl DESC
OPTION (MAXRECURSION 0);

-- Write the result
UPDATE dbo.T1
SET Result = @Result;

db>fiddle