删除数据表中的重复数据 示例表结构如下： [sql] mytable ----------- rowid int not null identity(1,1) primary key, col1 varchar(20) not null, col2 varchar(2048) not null, col3 tinyint not null 解决方法： [sql] delete from tablename where id n
删除数据表中的重复数据
示例表结构如下：
[sql] 
mytable  
-----------  
rowid int not null identity(1,1) primary key,  
col1 varchar(20) not null,  
col2 varchar(2048) not null,  
col3 tinyint not null  
解决方法：
[sql] 
delete from tablename  
where  id not in (select max(id)  
                  from   tablename  
                  group  by column1,  
                            column2,  
                            column3  
                  /*even if id is not null-able sql server treats max(id) as potentially  
                    nullable. because of semantics of not in (null) including the clause  
                    below can simplify the plan*/  
                  having max(id) is not null)   
如果是复合主键的话，需要把整个子查询放在exists子句中，exists用法如下：
[sql] 
delete from agent1 da    
 where exists(    
  select * from  customer cu    
   where grade=3    
     and da.agent_codecu.agent_code);