发有表
Company
ID Name LastEmployeeID
1 CSDN nullEmployee
ID CompanyID CompanyName Name
1 null CSDN ABC
2 null 163 DEF
3 null CSDN GHI
----------------------
现在要求更新Employee中的CompanyID 为Company表中Name和他的CompanyName一样的Company.ID
如果更新成功的话同时更新Company.LastEmployeeID为Employee.ID那语句要怎么写
谢谢
Company
ID Name LastEmployeeID
1 CSDN nullEmployee
ID CompanyID CompanyName Name
1 null CSDN ABC
2 null 163 DEF
3 null CSDN GHI
----------------------
现在要求更新Employee中的CompanyID 为Company表中Name和他的CompanyName一样的Company.ID
如果更新成功的话同时更新Company.LastEmployeeID为Employee.ID那语句要怎么写
谢谢
from Employee 如果更新成功的话同时更新Company.LastEmployeeID为Employee.ID这句有问题,Employee跟Company是多对一的关系
from Employee a
--------
RE:这里的LastEmployeeID没有多对一关系,他的意思就是取得最后更新Employee成功的Employee。ID
如上我理解为,Update应该成功两次,为Employee的1和3,这时Company的更新为null=>1=>3
谢谢
declare @Company table
(ID int,Name varchar(4),LastEmployeeID varchar(20))
insert into @Company
select 1,'CSDN',nulldeclare @Employee table
(ID int,CompanyID varchar(20),CompanyName varchar(4),Name varchar(3))
insert into @Employee
select 1,null,'CSDN','ABC' union all
select 2,null,'163','DEF' union all
select 3,null,'CSDN','GHI'update @Employee
set CompanyID=b.ID
from @Employee a left join @Company b on a.CompanyName=b.Nameupdate @Company
set LastEmployeeID=b.ID
from @Company a left join (select CompanyName,max(ID) as ID from @Employee
group by CompanyName) b on a.Name=b.CompanyNameselect * from @Company
/*
ID Name LastEmployeeID
----------- ---- --------------------
1 CSDN 3
*/
select * from @Employee
/*
ID CompanyID CompanyName Name
----------- -------------------- ----------- ----
1 1 CSDN ABC
2 NULL 163 DEF
3 1 CSDN GHI
*/
update
Employee
set
CompanyID=b.ID
from
Employee a left join Company b on a.CompanyName=b.Nameupdate
Company
set
LastEmployeeID=b.ID
from
Company a left join (select CompanyName,max(ID) as ID from Employee
group by CompanyName) b
on
a.Name=b.CompanyName
Employee
set
CompanyID=b.ID
from
Employee a left join Company b on a.CompanyName=b.Nameupdate
Company
set
LastEmployeeID=b.ID
from
Company a left join (select CompanyName,max(ID) as ID from Employee
group by CompanyName) b
on
a.Name=b.CompanyName
--------------------------
第二条的max(ID)能否取上面已经查询出来的ID号呢?