难题2，关于不同一表中的查询/插入问题！
假设有2个表T1,T2，都在一个数据库D1里面。
表T1如下：
ID Name Department
01 Zhao
02 Qian
03 Sun
04 Li
表T2如下：
ID Name Department
01 Zhao Marketing
02 Qian Service
03 Sun IT
01 Li Sales
要求是，如果T1中某一行A的ID和Name 与T2中的某一行B的ID和Name都相同（必须是同时相同）,那么把B中的Department插入到A中的Department对应列中。
所以显示结果应该是：
ID Name Department
01 Zhao Marketing
02 Qian Service
03 Sun IT
04 Li
请问这样的复杂SQL语句怎么写呢？
谢谢！
[解决办法]
update t1 set department=b.department from t1 a,t2 b where a.id=b.id and a.name=b.name
[解决办法]
update t1
department=t2.department
from t1,t2 where t1.id=t2.id and t1.name=t2.name
[解决办法]
update t1 set department=t2.department
from t1,t2 where t1.id=t2.id and t1.name=t2.name
[解决办法]
--如果是查詢
Select
A.ID,
A.Name,
IsNull(B.Department, A.Department) As Department
From
T1 A
Inner Join
T2 B
On A.ID = B.ID And A.Name = B.Name
--如果是更新
Update
A
Set
Department = IsNull(B.Department, A.Department)
From
T1 A
Inner Join
T2 B
On A.ID = B.ID And A.Name = B.Name
[解决办法]
如果你的實際情況和你舉的例子一樣，只是數據量上有差異，用
Update
A
Set
Department = B.Department
From
T1 A
Inner Join
T2 B
On A.ID = B.ID And A.Name = B.Name
這個更新沒有問題
