Delete duplicate data from a data table

Example of table structure for deleting duplicate data in a data table: [SQL] MyTable ----------- primary (2048) primarykey, Col1varchar (20) notnull, Col2varchar () notnull, Col3tinyintnotnull solution: [SQL] Explain

Example of table structure for deleting duplicate data in a data table: [SQL] MyTable ----------- RowID int not null identity (2048) primary key, Col1 varchar (20) not null, Col2 varchar) not null, Col3 tinyint not null solution: [SQL] DELETE FROM TableName WHERE ID N

Delete duplicate data from a data table

The structure of the example table is as follows:

[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

Solution:

[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)

If it is a composite primary key, you need to put the entire subquery in the exists clause. The EXISTS usage is as follows:

[SQL]

Delete from agent1 da

Where exists (

SELECT * FROM customer cu

WHERE grade = 3

AND da. agent_code <> cu. agent_code );