一次update动作中T-SQL与PL/SQL的差异

现有两张表，Tbl_Employee和Tbl_Department。表中数据如下（姑且不论表结构设计合不合理）：

1.Tbl_Employee 

2.Tbl_Department

 

其中，Tbl_Employee中的dept_name栏位数据有误，要求用Tbl_Department中的数据来更正。

1.PL/SQL写法：

update Tbl_Employee emp
set emp.dept_name = (
     select distinct dept.dept_name
     from Tbl_Department dept
     where dept.dept_id = emp.dept_id
);

若是T-SQL也是以上写法，则会报错！想想，可以用游标来实现。于是：

2.T-SQL写法：

declare @deptID nvarchar(20);
declare tempTbl cursor for
    select dept_name from Tbl_Department;
open tempTbl
fetch next from tempTbl  into @deptID

while @@FETCH_STATUS = 0
begin
        update Tbl_Employee
        set  dept_name = (
           select distinct dept_name from Tbl_Department
           where dept_id = @deptID )
        where dept_id = @deptID;

        fetch next from tempTbl into @deptID

end

close tempTbl
deallocate tempTbl

 

 

 以上T-SQL能实现功能，但效率非常低。用以下语句会更好(本人以前不熟悉update...from...)：

update Tbl_Employee  
set name= b.name  
from TBL_Department b  
inner join Tbl_Employee a  
on a.emp_id=b.emp_id;