请教SQL语句问题
我有两个表
表1
stu_num course_code course_score
123 001 85
123 002 95
125 001 65
126 001 84
表2
stu_num course_code course_score
123 001 85
123 002 95
125 001 15
我想将表1中数据都给复制到表2中,但是不能有重复的记录(stu_num,course_code不能重复),当course_score值不一样的话,就将其更新.否则就插入新的记录.
得到表2的记录为:
stu_num course_code course_score
123 001 85
123 002 95
125 001 65
126 001 84
问题点数：0、回复次数：8Top
1 楼yesterday2000（一笑而过）回复于 2004-09-02 23:02:29 得分 0
insert into 表2 (stu_num,course_code,course_score)
select stu_num,course_code,course_score
from 表1
where convert(varchar (20),stu_num)+convert(varchar (20),course_code) not in
(select convert(varchar (20),stu_num)+convert(varchar (20),course_code) from 表2)
Top
2 楼zhouxinjie（zhouwei）回复于 2004-09-02 23:56:21 得分 0
但是那个更新是怎么实现的啊
在表1中
125 001 65
表2中
125 001 15
帮帮我!
Top
3 楼yesterday2000（一笑而过）回复于 2004-09-03 00:10:12 得分 0
create table tb1 (stu_num int,course_code char (3),course_score int)
create table tb2 (stu_num int,course_code char (3),course_score int)
select * from tb2
insert into tb1
select 123,'001',85
union
select 123,'002',95
union
select 125,'001',65
union
select 126,'001',84
insert into tb2
select 123,'001',85
union
select 123,'002',95
union
select 125,'001',15
-----------------------------
UPDATE tb2 set course_score=b.course_score from tb2 a,tb1 b where a.stu_num=b.stu_num and a.course_code=b.course_code
insert into tb2 (stu_num,course_code,course_score)
select stu_num,course_code,course_score
from tb1
where convert(varchar (20),stu_num)+convert(varchar (20),course_code) not in
(select convert(varchar (20),stu_num)+convert(varchar (20),course_code) from tb2)
---------------------
select * from TB2Top
4 楼zonelive（peter）回复于 2004-09-03 00:26:32 得分 0
insert tb2
select a.* from tb1 a inner join tb2 b on a.stu_num<>b.stu_num or a.course_code <>b.course_code
或:
insert tb2
select * from tb1 a where not exists
(select 1 from tb2 b where a.stu_num=b.stu_num or a.course_code=b.course_code )
Top
5 楼jancyer（jancyer）回复于 2004-09-03 00:26:46 得分 0
把重复的记录先删除掉，再更新！
create table ta(stu_num int , course_code char(10),course_score int)
insert ta select 123 , '001', 85
union all select 123 , '002', 95
union all select 125 , '001', 65
union all select 126 , '001', 84
create table tb(stu_num int , course_code char(10),course_score int)
insert tb select 123 , '001', 85
union all select 123 , '002', 95
union all select 125 , '001', 15
delete from tb where exists( select * from ta as t where tb.stu_num=t.stu_num and tb.course_code=t.course_code)
insert tb select * from ta
drop table ta
drop table tbTop
6 楼hero1981（英雄）回复于 2004-09-03 08:54:02 得分 0
upTop
7 楼General521（dhy）回复于 2004-09-03 17:24:55 得分 0
jancyer(jancyer) 的办法中应该用临时表Top
8 楼zhangzs8896（小二）回复于 2004-09-05 20:20:38 得分 0
不用删除，用 yesterday2000(一笑而过) 的方法就可以了啊Top
