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 |
If no size is defined while creating the database, what size will the database have?
Do you know sql server 2008 introduces automatic auditing?
What is bulkcopy in sql?
What is 3nf normalization form?
What is Outter Join?
what is sql injection in sql server?
Can we use Truncate command on a table which is referenced by FOREIGN KEY?
What gets stored inside msdb database?
Explain SELF JOIN ?
write the query for taking database restore in sql?
Can multiple columns be used in sql group by clause in ms sql server?
What is triggers and stored procedures?