TSQL A recursive update?

I'm wondering if exists a recursive update in tsql (CTE)

ID  parentID value
--  -------- -----
1   NULL     0
2   1        0
3   2        0
4   3        0
5   4        0
6   5        0

I it possible to update the column value recursively using e.g CTE from ID = 6 to the top most row ?

Answers


Yes, it should be. MSDN gives an example:

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;

Need Your Help

Images cannot be found in .jar file

java jar embedded-resource javax.imageio illegalargumentexception

This question is edited to include everything asked. New picture links will be added below.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.