Delete Duplicate Data - SQL

Delete Duplicate Data without Using primary key and Identity key:

Example:

Id	Name
1	Test
1	Test
1	Test

USE Test

Go

WITH Duplicates AS

(

    SELECT Name,Id,

      ROW_NUMBER() OVER (PARTITION BY Id,Name ORDER BY Id) AS 'RowNum'

    FROM dbo.TestDuplicates  

     

)delete FROM Duplicates WHERE RowNum>1

SELECT * FROM dbo.TestDuplicates

After using this:

Id            Name

1              Test

Post By : Urvish Patel

Blog By : Dipen Shah

Stay Tuned...We rocked...

Self Join

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
 A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

Examples:

USE ProductManagement

Go

SELECT DISTINCT pv1.ProductID, pv1.CategoryID

FROM dbo.Products pv1

INNER JOIN dbo.Products pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.CategoryID = pv2.CategoryID

ORDER BY pv1.ProductID

Post By : Urvish Patel

Blog By : Dipen Shah

Stay tuned for more... 

Always match datatypes in code with the columns in the database

It's important to make sure that your datatypes match across all layers in your application. For example, if a column's datatype is VarChar(50), you should have the code in queries and stored procedures use local variables of the same datatype.

Similarly, the ADO.NET code in the data layer should specify the same datatype and length.


Posted By : Dipen Shah
Stay Tuned