0

I have a table:

ID Desc ParentID
1 Test A 0
2 Test A 0
3 Test A 0
4 Test B 0
5 Test B 0
6 Test B 0

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:
1 Test A 0
2 Test A 1
3 Test A 1
4 Test B 0
5 Test B 4
6 Test B 4
Thom A
  • 88,727
  • 11
  • 45
  • 75
Scott Scriver
  • 17
  • 4
  • 1
    How do you know what value is the "parent" and which the "children"? Why is `3` not a child of `2` and why is `4` not a child of `5`? With what you have a windowed `MIN` would work, but I somehow doubt that is it *real* solution. – Thom A Sep 29 '22 at 16:08
  • MIN might work as honestly, that would suffice. Can this be done in one statement or would I need to use a cursor? – Scott Scriver Sep 29 '22 at 16:14
  • 1 statement... no cursor. Examples: https://stackoverflow.com/questions/1068447/update-with-two-tables – xQbert Sep 29 '22 at 16:46

2 Answers2

0

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]
ID Desc ParentID
1 Test A 0
2 Test A 1
3 Test A 1
4 Test B 0
5 Test B 4
6 Test B 4

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
ID Desc ParentID
1 Test A 0
2 Test A 1
3 Test A 1
4 Test B 0
5 Test B 4
6 Test B 4

fiddle

Dale K
  • 25,246
  • 15
  • 42
  • 71
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Worked great! This is the actual SQL : WITH CTE as ( SELECT MIN(JobTitleID) min_id, JobTitleDesc FROM JobTitles GROUP BY JobTitleDesc) SELECT JobTitleID, ta.JobTitleDesc, CASE WHEN JobTitleID = min_id THEN ParentJobTitleID ELSE min_id END as ParentJobTitleID FROM JobTitles ta JOIN CTE ON ta.JobTitleDesc = CTE.JobTitleDesc -- how do I turn this into an UPDATE for the JobTitles Table? – Scott Scriver Sep 29 '22 at 17:06
  • @ScottScriver i updated my answer, you can change the column names as before – nbk Sep 29 '22 at 17:14
  • Excellent. Thank you so very much for your help and quick response! – Scott Scriver Sep 29 '22 at 17:20
0

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;
Charlieface
  • 52,284
  • 6
  • 19
  • 43