Re: Update Parent-Child-Grandchild Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 14:17:12 +0530

Update what?
I am only seeing some Ids in the table!

Are you sure you only have to manage the
hierarchy with three levels? Is there the possibilty
of a fourth or fifth level.

To get some better ideas about managing hierarchies
google "Adjacent List model" and "Nested Set Model" 

-- 
Roji. P. Thomas
SQL Server Programmer
<anonymous@discussions.microsoft.com> wrote in message
news:1775301c44942$12564790$a301280a@phx.gbl...
> Hi All
>
> I have a Parent-Child-Grandchild table as defined by the
> DDL below.  What is the most efficient way in order to
> update the records as illustrated below:
>
> I am trying to update the parent at level 1 and then
> update the child at level 2 and the grandchild at level
> 3.  I am also trying to update the parent at level 2 and
> the child at level 3.
>
> Thanks in Advance for any assistance.
>
> ie.
>
> Lvl         Parent      Child       GrandChild
> ----------- ----------- ----------- ----------- 
> 1           123         NULL        NULL
> 2           12345       123         NULL
> 3           1234567     12345       123
> 1           456         NULL        NULL
> 2           45678       456         NULL
> 3           4567890     45678       456
>
> Desired Result:
>
> Lvl         Parent      Child       GrandChild
> ----------- ----------- ----------- ----------- 
> 1           321         NULL        NULL
> 2           54321       321         NULL
> 3           1234567     54321       321
> 1           654         NULL        NULL
> 2           87654       654         NULL
> 3           4567890     87654       654
>
>
>
> CREATE TABLE Data
> (
> Lvl INT NOT NULL,
> Parent INT NOT NULL,
> Child   INT,
> GrandChild INT
> )
> GO
>
> INSERT INTO Data SELECT 1, 123, NULL, NULL
> INSERT INTO Data SELECT 2, 12345, 123, NULL
> INSERT INTO Data SELECT 3, 1234567, 12345, 123
>
> INSERT INTO Data SELECT 1, 456, NULL, NULL
> INSERT INTO Data SELECT 2, 45678, 456, NULL
> INSERT INTO Data SELECT 3, 4567890, 45678, 456

Relevant Pages

  • Re: SQL, related records (quotes)
    ... Let's say for the hierarchy itself we decide on ... child parent ... child B to reference for referential integrity and what meaning the ... possible with a vertex involving itself as a root node of a hierarchy. ...
    (comp.databases.theory)
  • Re: [RFC][PATCH 1/2] memcg: res_counter hierarchy
    ... While several policy of hierarchy can be considered, ... create a child. ... prepare enough room in parent. ... One way to manage hierarchies other than via limits is to use shares (please see ...
    (Linux-Kernel)
  • Re: Re: [RFC][PATCH 1/2] memcg: res_counter hierarchy
    ... While several policy of hierarchy can be considered, ... there are no shared resource ... create a child. ... prepare enough room in parent. ...
    (Linux-Kernel)
  • Re: Missing Data in Cube / MDX Question
    ... It is probably more likely that the hierarchy should be the other way ... It sounds like your dimension records possibly start out in a parent ... EquipHierarchyKey int not null, ...
    (microsoft.public.sqlserver.olap)
  • [RFC][PATCH 1/2] memcg: res_counter hierarchy
    ... This patch tries to implements _simple_ 'hierarchy policy' in res_counter. ... dynamic hierarchy resource usage management in the kernel is not necessary ... create a child. ... prepare enough room in parent. ...
    (Linux-Kernel)