Updating a column based on parent information
I am starting on SQL Server and I have a question about the best way to do this.
I have a table that looks like this:
ID & Parent & nbsp; Level
1 & nbsp NULL; 0
2 1 1
3 1 1
4 2 2
5 & nbsp; 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 records are parent and level, and the database is organized in a tree structure. There are some records where the Level is set incorrectly, for example ID # 8. Parent 8 is 5 and ID 5 is 2, so Level 8 should be 3 and not 4. There are many wrong Level values in my table and I'm not sure if How to fix it. So far I have this:
UPDATE myTable
SET level = level-1
FROM myTable
WHERE ???.
I'm not sure how to fill in the WHERE part or is this the best way to do it. Any suggestions are gladly appreciated.
source to share
If you are using SQL Server 2005 or SQL Server 2008 you can use a recursive CTE (Common Table Expression). online article books are pretty straight forward, but here's how you can do it with your code.
- create a temporary table and insert values
CREATE TABLE dbo.ctetest (childid int primary key is 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
RECOVERY CONNECTION rc
ON ct.childid = rc.childid;
- Check results
SELECT *
FROM dbo.ctetest;
Here's the output from the above query:
Child 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
Note that I have tested the above code with SQL Server 2008. I am assuming it will work in SQL Server 2005 since CTEs were introduced in 2005.
source to share