From last id, recursively find parent id and fill column

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:

• From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with its occurrence in COL1 (step2)

• 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.

• 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?

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

StackExchange Database Administrators Q#325751, answer score: 2