CODEWITHSUNDEEP
sqlsql-serversql-server-2016
DreamTeK
DreamTeK

Reputation: 34267

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]

Upvotes: 0

Views: 162

Answers (1)

Squirrel
Squirrel

Reputation: 24773

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

Upvotes: 1

Related Questions