Delete Duplicate rows [Resolved]

Posted by Anweshabhowmick under Sql Server on 2/21/2011 | Points: 10 | Views : 2353 | Status : [Member] | Replies : 6
Can any one tell me how to delete duplicate rows in sql server and how to show the duplicate field in sql server
and explain what is the difference between Delete and Trancate

Plas help me.

Thanks




Responses

Posted by: Madhu.b.rokkam on: 2/21/2011 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
Difference between them can be found here
http://www.technoreader.com/Difference-between-Truncate-and-Delete-in-SQL.aspx

to delete duplicate rows
http://support.microsoft.com/kb/139444


Thanks and Regards
Madhu

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 2/21/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi,

Refer the following query's...
To Delete the duplicate rows...
If you check multiple columns means add those columns in Partiton by Col1,Col2,Col3
with delqry

as
(Select row_number() over(partition by ID,Name order by ID) as sNo,ID from TableName)
Delete from delqry where sNo > 1



To find the duplicate records....
If you check multiple columns means add that in group by
Select ID,Name from TableName group by ID,Name having count(*) > 1


Diff b/w Delete and Truncate
using Delete means we can rollback the data.
syntax: delete from TableName

using Truncate means cannot be rollback the data.
syntax: truncate table TableName


If any doubts ask me.

Cheers :)



Thanks,
T.Saravanan

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 2/21/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi,

If you have a permission for insert,update..means you also drop a table.
Syntax: Drop Table TableName


If your doubts solved means kindly mark as answer for other user's help.
Cheers :)


Thanks,
T.Saravanan

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anweshabhowmick on: 2/21/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks this help me a lot

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anweshabhowmick on: 2/21/2011 [Member] Starter | Points: 25

Up
0
Down
Which one delete the Table Parmanently?

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kunalverma31 on: 8/12/2011 [Member] Starter | Points: 25

Up
0
Down
u can even use this approach to meet your requirement.

http://www.dotnetfunda.com/Blogs/Kunalverma31/1712/how-to-delete-duplicate-records-in-sql-using-rank-function

Anweshabhowmick, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response