Delete Duplicate rows [Resolved]

Posted by Anweshabhowmick under Sql Server on 2/21/2011 | Points: 10 | Views : 3883 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

0	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

0	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

Latest Posts