update b set u5=(select top 1 u5 from c,a where c.empno=a.empno and c.empid=b.empid)
update b set u5=(select top 1 u5 from c,a where c.empno=a.empno and c.empid=b.empid
谢谢各位,我用下面语句给update了一下 update b set u5=c.u5 from a,b,c where C.EmpNo=A.EmpNo 不知道结果是不是正确?
---test---create table a(empno int,empid int) insert into a values(1,1) insert into a values(2,2) create table b(empid int,u5 int) insert into b values(1,3) insert into b values(2,4) create table c(empno int,u5 int) insert into c values(1,6) insert into c values(2,5)update b set b.u5=t.u5 from b,(select a.empno,a.empid,c.u5 from c inner join a on a.empno=c.empno) as t where b.empid=t.empidselect * from bdrop table a drop table b drop table c--result empid u5 1 6 2 5
--test create table a(empno int,empid int) insert into a values(1,1) insert into a values(2,2) create table b(empid int,u5 int) insert into b values(1,3) insert into b values(2,4) create table c(empno int,u5 int) insert into c values(1,6) insert into c values(2,5)update b set u5=c.u5 from a,b,c where c.empno=a.empnoselect * from bdrop table a drop table b drop table c
大家都这么抄,错误从第一个抄到第三个declare @a table (EmpNo int, EmpId int) insert into @a values (1,1) insert into @a values (2,2) insert into @a values (3,3) declare @b table (EmpId int, u5 int) insert into @b values (1,1111) insert into @b values (2,2222) insert into @b values (3,3333) declare @c table (EmpNo int, u5 int) insert into @c values (1,11) insert into @c values (2,22) insert into @c values (3,33)update b set u5=c.u5 from @a as a,@b as b,@c as c where C.EmpNo=A.EmpNo and b.EmpID=a.EmpIDselect * from @b结果如下 EmpId u5 ----------- ----------- 1 11 2 22 3 33
update b set u5=c.u5
from a,b,c
where C.EmpNo=A.EmpNo
不知道结果是不是正确?
insert into a values(1,1)
insert into a values(2,2)
create table b(empid int,u5 int)
insert into b values(1,3)
insert into b values(2,4)
create table c(empno int,u5 int)
insert into c values(1,6)
insert into c values(2,5)update b
set b.u5=t.u5
from b,(select a.empno,a.empid,c.u5 from c inner join a on a.empno=c.empno) as t
where b.empid=t.empidselect * from bdrop table a
drop table b
drop table c--result
empid u5
1 6
2 5
create table a(empno int,empid int)
insert into a values(1,1)
insert into a values(2,2)
create table b(empid int,u5 int)
insert into b values(1,3)
insert into b values(2,4)
create table c(empno int,u5 int)
insert into c values(1,6)
insert into c values(2,5)update b set u5=c.u5
from a,b,c
where c.empno=a.empnoselect * from bdrop table a
drop table b
drop table c
insert into @a values (1,1)
insert into @a values (2,2)
insert into @a values (3,3)
declare @b table (EmpId int, u5 int)
insert into @b values (1,1111)
insert into @b values (2,2222)
insert into @b values (3,3333)
declare @c table (EmpNo int, u5 int)
insert into @c values (1,11)
insert into @c values (2,22)
insert into @c values (3,33)update b set u5=c.u5
from @a as a,@b as b,@c as c
where C.EmpNo=A.EmpNo and b.EmpID=a.EmpIDselect * from @b结果如下
EmpId u5
----------- -----------
1 11
2 22
3 33