删除数据表中的重复数据

删除数据表中的重复数据 示例表结构如下： [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_code<>cu.agent_code);