Welcome to weblogs.com.pk Sign in | Join | Help

Tsql : Update From / Self Join

The other day I received a query on how to update rows in a table having from clause and self join needs to be used…I thought its interesting scenario and should document it for future references…

Let me explain the problem with a scenario…Lets consider a table in which hierarchy is implemented using Parent / Child to self; something like

create table Employees
(
     EmployeeIndex int,
     ManagerIndex int, --For Parent / Child relationship
     Name nvarchar(50)
);
insert into Employees (employeeindex, managerindex, name) values (1, null, 'Manager');
insert into Employees (employeeindex, managerindex, name) values (2, 1, 'Developer');
insert into Employees (employeeindex, managerindex, name) values (2, 1, 'Tester');

And then lets say we added a column; ProjectIndex pointing to the Projects table key on which these employees working; and lets just update the manager row.

alter table Employees add ProjectIndex int;
update employees set projectindex = 1 where employeeindex = 1;

Now we need to update the Developer and Tester rows…the query will be:

update employees
set projectindex = m.projectindex
from employees join employees m on employees.managerindex = m.employeeindex

Important thing to note is; that we are not aliasing the table in the from clause and that is representing the updating table and its getting joined to the (same) table having alias and that alias is distinguishing it onwards!

If we want to alias the updating table; the update query will become

update members --Note the alias is used which we used in from clause below to represent the updating table
set projectindex = managers.projectindex
from employees members join employees managers on members.managerindex = manager.employeeindex
Published Thursday, October 08, 2009 9:57 AM by khurram
Filed under: ,

Comments

No Comments

New Comments to this post are disabled