全部
基础类
应用实例
新技术前沿

数据库中有两条记录的各列数值都一模一样，请问怎样删除一条？

pgz_007 2008-05-22 03:59:34

直接在企业管理器里面打开表的记录发现无法删除。
请问有什么好办法吗？

...全文

244 点赞收藏 9

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

Herb2 2008-05-23

declare @s varchar(8000)



alter table PricesMonthly add id int,checks bigint

set @s ='

declare @i int set @i = 1

update PricesMonthly set id = @i,@i= @i + 1,checks = checksum(*)

delete a from  PricesMonthly a where exists(select  1 from PricesMonthly where id<a.id and checks=a.checks)

alter table PricesMonthly drop column id,checks'

exec(@s)

select * from PricesMonthly

回复赞

pt1314917 2008-05-23





select distinct * into # from ta 



truncate table ta 



insert into ta select * from #

回复赞

Herb2 2008-05-23

alter table ta add id int

declare @i int

set @i = 1

update ta set id = @i,@i= @i + 1

delete a from  ta a where exists(select  1 from id<a.id and 字段比较)

alter table ta drop table id

回复赞

cdzch 2008-05-23

select distinct * into # from ta

truncate table ta
insert into ta select * from ta

回复赞

bing110 2008-05-22

呵呵，删除完全重复的记录。以上楼主的方法的确不错。之前遇到过要删除某些指定字段的记录，也在这里发一下。



declare @max integer,@id integer,@idcode 

　　declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1

　　open cur_rows

　　fetch cur_rows into @id,@max

　　while @@fetch_status=0

　　begin

　　select @max = @max -1

　　set rowcount @max

　　delete from 表名 where 主字段 = @id

　　fetch cur_rows into @id,@max

　　end

　　close cur_rows

　　set rowcount 0

回复赞

dawugui 2008-05-22

select * , id = identity(int,1,1) into tmp from tb

delete from tmp t where id not in (select min(id) from tmp where 关键字 = t.关键字)
delete from tb
insert into tb select *(除了id字段) from tmp
drop table tmp

回复赞