how to delete duplicate rows in sql server2005
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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?
What is snapshot replication?
How to create a new table in a given schema?
what are default? Is there a column to which a default cant be bound?
What are the various Operating system files that every SQL server 2005 database has and what is the purpose.
what is hash table
What is the difference between the following two sql statements select count(*) from <tablename> select count(col_name) from <tablename>
What are key, name and value columns of an attribute? : sql server analysis services, ssas
What are the steps you will take to improve the performance of a poor performing query?
Explain cursor as data base object?
What is check constraint in sql server?