Skip to content
Advertisement

SQL UPDATE column with self referenced id from another column

I want to update the ParentId of each row that is not null

With the Id from the row that has a TemplateId Matching ParentId

Id ParentId TemplateID
1001 NULL 86
1002 86 41
1003 43 44
1004 NULL 43
1005 44 73

Desired results:

Id ParentId TemplateID
1001 NULL 86
1002 1001 41
1003 1004 44
1004 NULL 43
1005 1003 73

The way I am doing it seems extremely convoluted. Is there a simpler way?

  UPDATE
    [dbo].[tbl]
  SET
    [ParentID] = [z].[ItemID]
  FROM
    [dbo].[tbl]
  JOIN (
    SELECT
      [x].[ParentID]
    FROM
      [dbo].[tbl]
    JOIN (
      SELECT DISTINCT
        [ParentID]
      FROM
        [dbo].[tbl]
      WHERE
        [ParentID] > 0
    ) [x]
    ON
      [dbo].[tbl].[TemplateID] = [x].[ParentID]
  ) [z]
  ON
    [dbo].[tbl].[ParentID] = [z].[ParentID]

Advertisement

Answer

This should gives you what you want. Self Join with ParentID = TemplateID


UPDATE t1
SET    ParentID = t2.ItemID
FROM   tbl t1
       INNER JOIN tbl t2 ON t1.ParentID = t2.TemplateID

dbfiddle demo

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement