studentid name age scanId
1 aa 20 ukkuk
2 bb 25 ucuco
3 cc 40 koiih
commentid comment scanId student_id
1 ok ukkuk
2 approve ukkuk
3 again ukkuk
4 test ukkuk
5 ok ucuco
6 ok ucuco
7 no koiih
8 yes koiih
9 yes koiih请问如何把comment表里的student_id根据scanId的值一次性填入
1 aa 20 ukkuk
2 bb 25 ucuco
3 cc 40 koiih
commentid comment scanId student_id
1 ok ukkuk
2 approve ukkuk
3 again ukkuk
4 test ukkuk
5 ok ucuco
6 ok ucuco
7 no koiih
8 yes koiih
9 yes koiih请问如何把comment表里的student_id根据scanId的值一次性填入
update comment set student_id=b.id from comment a left join student b on a.scanid=b.scanid
set student_id=student.id
from comment
inner join student on comment.scanid=student.scanid
if object_id('student ')is not null drop table student
go
create table student(id int , name varchar(10), age int, scanId varchar(10))
insert student select 1, 'aa' , 20 , 'ukkuk'
insert student select 2, 'bb' , 25, 'ucuco'
insert student select 3, 'cc' , 40, 'koiih'
if object_id('comment')is not null drop table comment
go
create table comment(id int, comment varchar(10), scanId varchar(10), student_id int)
insert comment select 1, 'ok' ,'ukkuk' ,null
insert comment select 2, 'approve', 'ukkuk' ,null
insert comment select 3, 'again' , 'ukkuk' ,null
insert comment select 4, 'test' , 'ukkuk' ,null
insert comment select 5, 'ok' , 'ucuco' ,null
insert comment select 6, 'ok' , 'ucuco' ,null
insert comment select 7, 'no' , 'koiih' ,null
insert comment select 8, 'yes' , 'koiih' ,null
insert comment select 9, 'yes' , 'koiih',null
update comment set student_id=student.id from student where comment.scanid=student.scanid
select * from comment
/*id comment scanId student_id
----------- ---------- ---------- -----------
1 ok ukkuk 1
2 approve ukkuk 1
3 again ukkuk 1
4 test ukkuk 1
5 ok ucuco 2
6 ok ucuco 2
7 no koiih 3
8 yes koiih 3
9 yes koiih 3
*/
update comment
set student_id=student.id
from student,comment
where comment.scanid=student.scanid
comment a inner join student b on a.scanid=b.scanid
update comment set comment.student_id = (select id from student where student_id=
comment .student_id)这样也行,呵呵
update comment set student_id =student.id
from comment,student
where comment.scanid=student.scanid
update comment set student_id=值 from comment where scanId=值;
若是外键,得先插入主键表,再改或添外键表.