updating column based off of parent information

Question

Hi, I am a beginner at SQL Server and I have a question about how best to do this.

I have a table that looks like this:

ID      Parent     Level
1      NULL        0
2       1          1
3       1          1
4       2          2
5       2          2
6       3          2
7       2          2
8       5          4
9       4          3
10      6          3
11      6          3

As you can see, all the entries have a Parent and a Level and the database is organized in a tree structure. There are some entries where the Level is set incorrectly such as entry ID #8. The Parent of 8 is 5 and ID 5 has a level of 2 so the level of 8 should be 3 and not 4. There are many incorrect Level values in my table and I'm not sure how to fix this. So far I have this:

UPDATE myTable
SET level=level-1
FROM myTable
WHERE ???;

I am not sure how to fill in the WHERE part or whether this is the best way to do this. Any suggestions are gladly appreciated.

Answer 1

If you are using SQL Server 2005 or SQL Server 2008, then you can use a recursive CTE (common table expression). The books online article is pretty straight forward, but here's how you can do it with your code.

-- Create temporary table and insert values

CREATE TABLE dbo.ctetest (childid int primary key not null, parentid int null, level int null);

INSERT INTO dbo.ctetest (childid, parentid) SELECT 1, NULL;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 2, 1;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 3, 1;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 4, 2;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 5, 2;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 6, 3;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 7, 2;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 8, 5;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 9, 4;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 10, 6;

INSERT INTO dbo.ctetest (childid, parentid) SELECT 11, 6;

-- Update table with level data from recursive CTE

WITH recursivecte (childid, parentid, level)

AS

(SELECT childid
        , parentid
        , 'level' = 0
FROM dbo.ctetest
WHERE parentid IS NULL
UNION ALL
SELECT ct.childid
        , ct.parentid
        , 'level' = rc.level + 1
FROM dbo.ctetest ct
JOIN recursivecte rc
        ON ct.parentid = rc.childid)

UPDATE ct

SET level = rc.level

FROM dbo.ctetest ct

JOIN recursivecte rc

ON ct.childid = rc.childid;

-- Verify results

SELECT *

FROM dbo.ctetest;

Here's the output from the above query:

Child ID Parent ID Level

1 NULL 0

2 1 1

3 1 1

4 2 2

5 2 2

6 3 2

7 2 2

8 5 3

9 4 3

10 6 3

11 6 3

Please note I tested the above code using SQL Server 2008. I'm assuming it will work in SQL Server 2005 since CTE's were introduced in 2005.