We help IT Professionals succeed at work.

how to update a table recursively sql server 2005?

Medium Priority
470 Views
Last Modified: 2012-05-07
Hello Experts,


There is an Employee table with (Empid, empname, ManagerID, salary)

i need toi update salary for all employees of s perticular manager? I am trying with recursive CTE syntax. But no luck.

Could you please help me on this?

Thanks,
itsvtk



Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
why cant you get the ids of all the employee reporting to the manager

;WITH CTE_EmpList ( ManagerID )
AS (
-- anchor members
SELECT e.ManagerID
FROM Employee e
WHERE ManagerID = @managerID

UNION ALL
-- recursive members
SELECT e.ManagerID
FROM Employee e
INNER JOIN CTE_EmpList d ON e.ManagerID = d.EmployeeID
)
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
Do you need recursion? What about:

UPDATE Employee SET ...
WHERE ManagerID = @MgrID
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
SharathData Engineer
CERTIFIED EXPERT

Commented:
Is it possible to explain with some sample data and how do you want to update?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.