declare @row int
select @row=count(*) from ?1 where ?2
set rowcount @row-1
delete ?1 where ?2
以上?1表示表名称,?2表示选择这些重复记录的条件
select @row=count(*) from ?1 where ?2
set rowcount @row-1
delete ?1 where ?2
以上?1表示表名称,?2表示选择这些重复记录的条件
这句好象有问题,我在SQL 6.5 中执行不成功!
insert into TempTable select distinct * from TableName
truncate table TableName
insert into TableName select * from TempTable
drop table TempTable
将TableName换为表名,就可以了
删除重复的为:
delete a.* from tbla as a join tbla as b a.id>b.id and a.name=b.name或delete * from tbla where id in(select a.id from tbla as a join tbla as b on a.id>b.id and a.name=b.name)
删除重复的为:
delete from tbla where id in (select a.id from tbla as a join tbla as b on a.id>b.id and a.name=b.name)
转述如下:
用临时表
select distinct * into #tmp from table1 where 条件
delete from table1 where 条件
insert table1 select * from #tmp
drop table #tmp
SCATTER MEMVAR
打开视图
APPEND BLANK
GATHER MEMVAR
就OK了!
DELETE FROM TABLE WHERE KEY1 IN (SELECT KEY1 FROM TABLE1 GROUP BY KEY1 HAVING(COUNT(*))>1)
TABLE1 為表名
KEY1為唯一欄位,可用組合鍵,用+連接
按鍵值分組統計比較好
DELETE FROM TABLE1 WHERE KEY1 IN (SELECT KEY1 FROM TABLE1 GROUP BY KEY1 HAVING(COUNT(*))>1)
TABLE1 為表名
KEY1為唯一欄位,可用組合鍵,用+連接 更正
delete from source_table
insert into source_table select distinct source_table.所有字段的列表 from #tmp
drop table #tmp