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

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

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
回复
liangCK 2008-05-22
[Quote=引用 1 楼 happyflystone 的回复:]
select distinct * into # from ta

truncate table ta
insert into ta select * from ta
[/Quote]
回复
Limpire 2008-05-22
一模一样?

少量数据重复,先删除再insert

大量数据重复

select distinct * into # from tb
truncate table tb
insert tb select * from #
回复
-狙击手- 2008-05-22
select distinct * into # from ta

truncate table ta
insert into ta select * from ta
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-05-22 03:59
社区公告
暂无公告