how to delete duplicate rows in sql server2005

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

More SQL Server Interview Questions

can we call stored Procedure in Function in Sql Server 2000 and vice versa.

3 Answers   C3I, eSoft, iPRO Solutions, Wipro,


What is a view and what are its advantages?

0 Answers  


What is snapshot replication?

0 Answers  


How to create a new table in a given schema?

0 Answers  


what are default? Is there a column to which a default cant be bound?

1 Answers  






What are the various Operating system files that every SQL server 2005 database has and what is the purpose.

0 Answers   Microsoft,


what is hash table

3 Answers   ILFS, Teledata,


What is the difference between the following two sql statements select count(*) from <tablename> select count(col_name) from <tablename>

5 Answers   247Customer,


What are key, name and value columns of an attribute? : sql server analysis services, ssas

0 Answers  


What are the steps you will take to improve the performance of a poor performing query?

0 Answers  


Explain cursor as data base object?

0 Answers  


What is check constraint in sql server?

0 Answers  


Categories