I have a table:

I need help with an SQL Update Script that will take the first distinct Desc, keep the parentID of 0, but make all other instances of the Desc have a ParentID of the first.

So, by the end, it will be:

Sample Result Data:

CodePudding user response：

Desc is a reserved word so it is a bad choice, the rest can be solved with a cte to get the parent of all Desc

WITH CTE as (
  SELECT MIN(ID) min_id, [Desc] FROM TableA GROUP BY [Desc])
SELECT [ID], ta.[Desc], CASE WHEN ID = min_id THEN ParentID ELSE min_id END as ParentID 
  FROM TableA ta JOIN CTE ON ta.[Desc] = CTE.[Desc]

fiddle

An Update is actually also easy

WITH CTE as (
  SELECT MIN(ID) min_id, [Desc] FROM TableA GROUP BY [Desc])
, CTE2 AS (SELECT [ID], CASE WHEN ID = min_id THEN ParentID ELSE min_id END as ParentID 
  FROM TableA ta JOIN CTE ON ta.[Desc] = CTE.[Desc])
UPDATE TableA SET ParentID =  (SELECT ParentID FROM CTE2 WHERE ID = TableA.ID)

6 rows affected

SELECT * FROM TableA

fiddle

CodePudding user response：

You can do this without any self-joins, using a window function

WITH cte AS (
    SELECT *,
      MIN(a.ID) OVER (PARTITION BY a.[Desc]) AS min_id
    FROM TableA a
)
UPDATE cte
SET ParentId = min_id
WHERE ID <> min_id;