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