CODEWITHSUNDEEP
sqlsql-server
sathish
sathish

Reputation: 57

Cross join on single table

Please suggest how to use cross join for the below table

ID level parentid grandparentid
1   1     205     280
2   1     206     281
3   2    null     280
4   2    null     280
5   2    null     281

Now I want to be like below in update the own table

ID level parentid grandparentid
1   1     205     280
2   1     206     281
3   2     205     280
4   2     205     280
5   2     206     281

I want to fill the parent id if grandparentid matched on level 1 and level 2 using update statement to update the null values.

Upvotes: 0

Views: 541

Answers (4)

Brennan Pope
Brennan Pope

Reputation: 1083

First of all, a grandparent may have many children, or parentId's in this case, so it is not a good assumption that just because a parent-child relationship exists in level 1, it will also exist in level 2.

So, I advise digging into the data a bit more to see if there is any way you can find the true relationships for level 2.

If you are intent on running the update, you should at least ensure that there is one and only one parentId for every grandparentId assigned on level 1 before you update the row on level 2.

Here is an example:

DECLARE @Table TABLE
(
    ID INT PRIMARY KEY IDENTITY
    ,Level INT
    ,ParentId INT
    ,GrandparentId INT
);

INSERT INTO @Table
(
    Level
    ,ParentId
    ,GrandparentId
)
VALUES
    (1,     205,     280)
    ,(1,    206,     281)
    ,(1,    207,     282)
    ,(1,    208,     282)
    ,(2,    null,    280)
    ,(2,    null,    280)
    ,(2,    null,    281)
    ,(2,    null,    282);

SELECT * FROM @Table;

UPDATE T SET ParentId = T2.TheOneAndOnlyParentId 
FROM
    @Table T
JOIN
(
    SELECT
        GrandparentId
        ,MAX(ParentID) AS TheOneAndOnlyParentId
    FROM
        @Table
    WHERE
        ParentId IS NOT NULL
        AND Level = 1
    GROUP BY 
        GrandparentId
    HAVING
        MIN(ParentID) = MAX(ParentID)
) T2
    ON T.GrandparentId = T2.GrandparentId
WHERE
    T.Level = 2;

SELECT * FROM @Table;

Notice how one level-two row was not updated. This is because the level-one relationship was unclear.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can do this with JOIN also:

DECLARE @t TABLE
    (
      ID INT ,
      Level INT ,
      ParentID INT ,
      GrandparentID INT
    )

INSERT  INTO @t
VALUES  ( 1, 1, 205, 280 ),
        ( 2, 1, 206, 281 ),
        ( 3, 2, NULL, 280 ),
        ( 4, 2, NULL, 280 ),
        ( 5, 2, NULL, 281 )


UPDATE  t1
SET     ParentID = t2.ParentID
FROM    @t t1
        LEFT JOIN @t t2 ON t1.GrandparentID = t2.GrandparentID
WHERE   t1.ParentID IS NULL

SELECT  * FROM    @t

Output:

ID  Level   ParentID    GrandparentID
1   1       205         280
2   1       206         281
3   2       205         280
4   2       205         280
5   2       206         281

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

UPDATE t2 SET parentid=t1.parentid
--SELECT *
FROM Table t1
INNER JOIN Table t2 ON t1.grandparentid=t2.grandparentid
    AND t2.parentid IS NULL
    AND t1.Level=t2.Level-1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You can get the results in a select. I think the easiest way is a correlated subquery (or outer apply):

select t.id, t.level,
       coalesce(t.parentid, 
                (select top 1 t2.parentid
                 from table t2
                 where t2.grandparentid = t.grandparentid and
                       t2.parentid is not null
                )
               ) as parentid,
       t.grandparentid
from table t ;

You can incorporate this into an update statement, if you actually want to change the data.

update t
    set parentid = (select top 1 t2.parentid
                    from table t2
                    where t2.grandparentid = t.grandparentid and
                          t2.parentid is not null
                   )
    from table t
    where parentid is null;

Upvotes: 3

Related Questions