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