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 |
What are different types of data sources?
What command must you use to include the not null constraint after a table has already been created?
which database is best to use in oracle and sql server? explain reasons?
If i am handling 150 servers then how to check the active jobs of all the servers?
Why we use functions in sql server?
What is #temp and @table variable in SQL server?
How efficient you are in oracle and SQL server?
what is Buffer cash and Log Cache? Can you Explain it?
What are different types of statements that are supported by sql?
How to optimize stored procedures in sql server?
Explain the commands in sql server?
What is change data capture (cdc) in sql server 2008?