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 is raiseerror? What is raiseerror?
How to find out the list schema name and table name for the database?
Why would you call update statistics?
Do comments need to go in a special place in sql server 2005?
Why are you getting errors when creating a new odbc dsn?
What is the difference between functions and scalar functions?
What does the automatic recovery do?
How to select some specific columns from a table in a query in ms sql server?
Hi all, I need query help for below senorio, could you please help me. TableName = City CITYID ContinuationID CITYNAME 1 1 SAN 1 2 DIEGO 2 1 SAN 2 2 FRANCISCO 3 1 CHICAGO 4 1 NEW 4 2 YORK 4 3 CITY Could you please help me to write a generalized SQL that returns results as given below in the Query result CITYID NAME1 NAME2 NAME3 NAME4 NAME5 1 SAN DIEGO 2 SAN FRANCISCO 3 CHICAGO 4 NEW YORK CITY
Can I work with several databases simultaneously? : sql server management studio
what's the difference between delete table and truncate table commands? : Sql server database administration
How do you fine the performance tunning?