How To delete Duplicate Rows in database table????

[BabuLives]

How To delete Duplicate Rows in database table????

Is there anyway to do this without the use of cursor or temporary
table....

Regards,

Satheesh

[Siva CH]

You can delete the duplicate rows in the tables, using the following T-SQL statements

 

The below Statement helps to find duplicate rows in a table ( col1 being primarykey, col2 being duplicates for example)

SELECT Col1,col2 FROM TABLE

GROUP BY Col1,col2

HAVING count(col1) > 1
 

Apply the DELETE statement upon the above resultset.

 

Regards,

SivaDude
 

[BabuLives]

hai siva...

if the table has...

col1 col2
1 babu
1 babu
2 satheesh
2 satheesh
3 siva

No i want the table without duplicates as..
1 babu
2 satheesh
3 siva

Regards,
Satheesh

[BabuLives]

[karthick]

Use like this :

Select Distinct col1, col2 from [tableName]

-- this will give you only the distinct ones. And if the col1 is the
unique ID column then you can increase to the Identity seed to 1 and
make it primary key so that it will be unique to all the rows.

Hth,
Karthick

[Siva CH]

Hi Sateesh,

SELECT Col1,col2 FROM TestTab GROUP BY Col1,col2 HAVING COUNT(Col1) >= 1

 

The above query gives the result you wanted. I hope this helps.

 

Thanks,
Siva

[Laks_win]

Hi,

Method 1:

Note: Table must have a "primary key/Unique" key fields. Bcz we are
going to filter the records using that field only. Consider "ID" as a
primary key.

delete from Table1
where (ID) not in
(select min(ID) from Table1 group by FieldName)

Method 2:

If u r not having primary key, then it needs "Temp table" to complete.

Steps
1) Copy the required rows into the temp table.
2) Delete all the rows in the main(employee) table
3) Shift back the records from temp table
4) Delete the temp table

Hope this helps

Regards
Lakshmi Narayanan.R