HOW TO ELIMINATE DUPLICATES ROWS FROM A TABLE

EXAMPLE1:

create table EE(col1 int, col2 int, col3 char(50))

insert into EE values (1, 1, 'data value one')

insert into EE values (1, 1, 'data value one')

insert into EE values (1, 2, 'data value two')

SELECT * FROM EE

--The first step is to identify which rows have duplicate primary key values:

SELECT col1, col2, count(*)

FROM EE

GROUP BY col1, col2

HAVING count(*) > 1

--This will return one row for each set of duplicate PK values in the table. The last column in this result is the number of duplicates for the particular PK value.

     

  --   If there are only a few sets of duplicate PK values, the best procedure is delete these manually on an individual basis. For example:

set rowcount 1

delete from EE

where col1=1 and col2=1

EXAMPLE2: