select * from forums_ksbm a, result b where
a.bknbcode_id=b.leibie and a.stuid_actived=1 and area!=20
and left(b.sfzh,6)=left(a.id_card,6)
and len(b.sfzh)=15 and len(a.id_card)=18
and substring(a.id_card,9,6)=substring(b.sfzh ,7,6)

所影响的条数是956行
update result set result.sign=1 from forums_ksbm a, result b where
a.bknbcode_id=b.leibie and a.stuid_actived=1 and area!=20
and left(b.sfzh,6)=left(a.id_card,6)
and len(b.sfzh)=15 and len(a.id_card)=18
and substring(a.id_card,9,6)=substring(b.sfzh ,7,6)

所影响的条数是945条
为什么会这样呢？
谢谢

你把第一个的956条写进临时表
然后和第2个的945条比较一下 不就知道那不一样的11条是什么了

update result set result.sign=1 from forums_ksbm a where
a.bknbcode_id=result.leibie and a.stuid_actived=1 and area!=20
and left(result.sfzh,6)=left(a.id_card,6)
and len(result.sfzh)=15 and len(a.id_card)=18
and substring(a.id_card,9,6)=substring(result.sfzh ,7,6)
直接这样写，看看更新几条

棉花糖斑竹，用你的语句更新的是945条记录
为什么会出现这种情况呢？

我把选出的数据放入一个临时表发现有十一条记录时重复的。是什么造成临时表中有重复数据呢？我不太明白。
谢谢！

select * from forums_ksbm a, result b where
a.bknbcode_id=b.leibie and a.stuid_actived=1 and area!=20
and left(b.sfzh,6)=left(a.id_card,6)
and len(b.sfzh)=15 and len(a.id_card)=18
and substring(a.id_card,9,6)=substring(b.sfzh ,7,6)

这个选出来的有重复数据

create table test1(id int,score int)
insert into test1 select 1,90
union all select 1,90
union all select 2,75

create table test2(id int,score int)
insert into test2 select 1,74

select * from test1,test2 where test1.id=test2.id //选出来的是两条记录

update test2 set test2.score=t.score from test1 t where test2.id=t.id //显示只更新一行