how to delete duplicate rows in sql server2005

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / kishoremca21

7 Answers
13498 Views
Cisco, CTS, HCL, IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete duplicate rows in sql server2005..

Answer / pritesh

By using temporary table, But it will delete exactly
duplicate rows NOT RECOMMANDABLE FOR HUGE TABLE. Query will
be.

SELECT DISTINCT * INTO #A FROM TABLE1

TRUNCATE TABLE TABLE1

INSERT INTO TABLE1
SELECT * FROM #A

Is This Answer Correct ?

30 Yes

3 No

how to delete duplicate rows in sql server2005..

Answer / arunkumar_mlx

WITH A
AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY
columnname_1,columnname_2 ORDER BY columnname_1) AS
duplicate FROM table_name
)
SELECT * FROM A WHERE duplicate>1
--DELETE FROM A WHERE duplicate>1

First select and you can find the row_number having more
than 1 rows.

Then delete them on comment of select stmt inside the
query..
decomment of that delete stmt will delete the duplicate
rows.

Is This Answer Correct ?

8 Yes

4 No

how to delete duplicate rows in sql server2005..

Answer / enis ertem

with DeleteDups as
(
Select * from TableA AS T1
where KeyCol <
(Select Max(Keycol) from TableA as T2
where t1.Id = T2.ID)
)

Delete from Delete Dups;

Is This Answer Correct ?

4 Yes

1 No

how to delete duplicate rows in sql server2005..

Answer / nittu

Delete from Tbl where Id NOT IN(
select Min(Id)
from tbl
Group By Colmn1,Column2)

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows in sql server2005..

Answer / surendra pal singh

delete from tablename where empname in (select empname from table name group by empname having empid>1)

where empname and empid are the columns of the table.
definately it will work

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows in sql server2005..

Answer / apps

select distinct eno,ename into temp_table from main_table
drop table main_table
-----
sp_rename temp_table,main_table

Is This Answer Correct ?

0 Yes

1 No

how to delete duplicate rows in sql server2005..

Answer / sundaravadivel g

Select top 1 * from table where a=1

select top 1* into table1 from table where a=1

delete from table

insert into table
select * from table1

Is This Answer Correct ?

1 Yes

7 No

Post New Answer