求一Update的Sql语句
有俩个表:
表student1:
studentName studentClass studentID
- - -
- - -
表student2:
studentName studentID
- -
- -
现在想根据stuedent2.studentID,更新student1.studentID
我是这么写的:
Update student1 set studentID = student2.studentID
where
student1.studentName =student2.studentName
可是不对啊,应该怎样写呢? 我只想用Update语句实现;
[解决办法]
Update student1 set studentID = student2.studentID
FROM student1 ,student2
where student1.studentName =student2.studentName
[解决办法]
update 表一 set 表一.A = 表二.B from 表一,表二 where 表一.C = 表二.D
[解决办法]
---Try
Update A
Set A.studentID=B.studentID
From
student1 A,student2 B
Where A.studentName=B.studentName
--Or
Update A
Set A.studentID=B.studentID
From
student1 A
Inner Join
student2 B
On A.studentName=B.studentName
[解决办法]
上面几位的答案是正确的，如果你的studentID是自增id的话则无法对其修改
[解决办法]
如果你的student1.studentID是自增id
试试下面的.
Set Identity_Insert student1 on
Update student1
set studentID = student2.studentID
From student2 --加这句
Where student1.studentName =student2.studentName
Set Identity_Insert student1 off
[解决办法]
估计是表中的studentName存在重名，而studentID 是唯一的。所以会发生错误。
[解决办法]
Set Identity_Insert student1 on
Set Identity_Insert student1 off
只能用于 自增id 的添加.
不能用于修改.
呵呵,说错了!
