sql server2005 查找重复数据并修改
--重复数据修改functiondeclare @cpbh varchar(32)
declare @id int
declare @tmp int
declare @num int
set @num=0
declare cur_cpbh1 cursor for
select prodno
from bv_fp_prodPic
where ismainpic='Y'
group by prodno , ismainpichaving count(ismainpic)>1 order by prodno
opencur_cpbh1;
fetch next from cur_cpbh1 into @cpbh;
while @@fetch_status=0
begin
print 'cpbh:' + @cpbh;
--set @tmp = (select count(prodno) from bv_fp_prodPic where ismainpic='Y' and prodno = @cpbh)
declare cur_id cursor
for
select seqnumber from bv_fp_prodPic where ismainpic='Y' and prodno = @cpbh order by seqnumber
open cur_id;
fetch next from cur_id into @id;
set @tmp = 0;
while @@fetch_status=0
begin
if @tmp >0
begin
update bv_fp_prodPic set ismainpic='N' where seqnumber = @id
end
fetch next from cur_id into @id;
set @tmp=@tmp + 1 ;
set @num=@num + 1 ;
end
close cur_id
deallocate cur_id
fetch next from cur_cpbh1 into @cpbh;
end
print @num
close cur_cpbh1
deallocate cur_cpbh1
页:
[1]