How do I set the parent id of a node using a query in SQL Server?

Question

I'm creating a stored procedure that adds a tree, inserts nodes into that tree and then updates the nodes to include the parent id. I'm updating the nodes to include the parent id, because the parent nodes are inserted at the same time as the child nodes so they don't have an id yet.

My issue is that I'm unable to create a query that will set the codes of the parent id in the column of the correct child nodes. I have tried JOIN, UNION, INTERSECT and subqueries, but I haven't been able to get it to work.

Update statement:

UPDATE [dbo].[Node]
SET ParentId = dn.Code
FROM [dbo].[Node] as dn
???

The @nodes variable is a table that contains the following:

node_code	parent_code
'001'	null
'002'	null
'003'	'001'
'004'	'003'

Existing data in Node Table, before trying to update ParentId:

id	Code	ParentId
00000000-0000-0000-0000-000000000001	'001'	null
00000000-0000-0000-0000-000000000002	'002'	null
00000000-0000-0000-0000-000000000003	'003'	null
00000000-0000-0000-0000-000000000004	'004'	null

The Node table is created like this (simplified because mine contains more columns):

CREATE TABLE [dbo].[Node]
(
  [Id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
  [Code] VARCHAR(MAX),
  [ParentId] UNIQUEIDNETIFIER FOREIGN KEY REFERENCES [Node](Id),
  [TreeId] UNIQUEIDENTIFIER FOREIGN KEY REFERENCES [Tree](Id)
)

My latest attempt was this, but this set the parent id as the id of the child whose parent I'm trying to add:

UPDATE [dbo].[Node]
SET ParentId = dn.Code
FROM [dbo].[Node] as dn
JOIN @nodes as n ON dn.Code = n.node_code
WHERE dn.TreeId = @treeId AND n.parent_code IN 
(SELECT n2.parent_code
 FROM [dbo].[Node] as dn2
 JOIN @nodes as n2 ON dn2.Code = n2.parent_code
 WHERE dn2.TreeId = @treeId)

Join Node, @nodes and another Node table (as parent) on parent_code and update id from parent. Show us your attempts, otherwise we have nothing to fix :) — Arvo, Dec 15 '20 at 12:46
Where are the values from `@Nodes` going in your table? The 2 columns in your variable are both a `varchar(3)` but there's only a `MAX` and 3 `uniqueidentifier` columns in the table `dbo.Node`. There's not even 1, let alone 2, `varchar` (not `MAX`) columns for the values. — Thom A, Dec 15 '20 at 12:47
Actual sample data, and expected results will likely help us help you here. — Thom A, Dec 15 '20 at 12:48
@Arvo when I join a the Node table a second time I get an error that `[dbo].[Node]` in the line `UPDATE [dbo].[Node]` is ambiguous. Do you know how to solve that? — Friso, Dec 15 '20 at 13:02
@Larnu the values of @nodes shows which codes should have which parent codes. It's a coupling table that's used to make this coupling, for instance the node with code '003' should have the `id` of the node with code '001' as it's `parentid` — Friso, Dec 15 '20 at 13:07
@Arvo your answer worked, I followed https://stackoverflow.com/a/34271364/1984657 to fix the ambiguous error. If you write your solution as an answer I'll accept it — Friso, Dec 15 '20 at 13:13

score 1 · Accepted Answer · answered Dec 15 '20 at 13:51

Posted as answer as requested.

Join Node, @nodes and another Node table (as parent) on parent_code and update id from parent, like this:

update chld
set parentid = prnt.id
from dbo.Node chld
inner join @Nodes n on n.node_code = chld.code
inner join dbo.Node prnt on prnt.code = n.parent_code

Jeffrey Eldredge · Answer 2 · 2020-12-15T13:24:01.023

0

If your @nodes table variable is indeed a fact table where you want to apply the value there to [ParentId] in [Node], then an UPDATE with a JOIN should work.

UPDATE t1
SET t1.ParentId = t2.parent_code
FROM Node t1
INNER JOIN @nodes t2 ON t1.Code = t2.node_code

edited Dec 15 '20 at 13:24

answered Dec 15 '20 at 13:01

Jeffrey Eldredge

899
7
12

When I do that the parentId becomes the same as the Id in all rows, so if Id = 1 then ParentId = 1, Id = 2 then ParentId = 2 and so on. – Friso Dec 15 '20 at 13:04
I might have just had a typo messing up the predicate for the join. But this is definitely how you're gonna get there. I checked it on my side real quick. It updates as I would expect. I've made edits; Check twice for me. – Jeffrey Eldredge Dec 15 '20 at 13:13

How do I set the parent id of a node using a query in SQL Server?

2 Answers2