Introduction
In this post we will learn how to implement ajax control toolkit Textbox watermark extender in Asp.net using c#. Watermark textbox in asp.net c#.
Previous Updates
In previous articles we have learnt Transaction Commit and Rollback in sql server with example.What is Blocking and Deadlock In SQL.
Problem
Many times we have face the similar problems with our record. Primary columns are always unique but sometimes we have duplicate entry of same type of record for different IDs in our database and that's the wired situation. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table.
Here is the one of the best way to overcome from this.
What is Lock and how to achieve lock on sql table.
To understand this with example here i am sharing the insert data and create table query .
Data In Table -
CREATE TABLE
LevelUp
(
[ID] INT
IDENTITY,
[FirstName] Varchar(100),
[LastName] Varchar(100),
[Address] Varchar(100),
)
GO
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('JP', 'Ji', 'India')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('JP', 'Ji', 'India')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('JP', 'Ji', 'India')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Jatin', 'P', 'UK')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Jatin', 'P', 'UK')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Nicks', 'J', 'ALM')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Nicks', 'J', 'ALM')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Nicks', 'J', 'ALM')
INSERT INTO
LevelUp([FirstName], [LastName], [Address])
VALUES ('Gaurav', 'N', 'RMN')
GO
SELECT *
FROM LevelUp
GO
|
Remove duplicate records in SQL
When we talking remove duplicate records it has two means one is temporary remove means return only unique records and second is remove all duplicate records directly from table . Here we examine both the situation.
There are many ways to Remove/Delete duplicate record from table.
1. Using Correlated Subquery
If you already have a identity column on your table, your work is half done. You can use a correlated subquery to get rid of the duplicates.
In a correlated subquery, first outer query is evaluated, the result from the outer query is used by an inner sub query for its evaluation, whatever the outcome of the inner sub-query is again used by the outer query to get the final resultset.
Select Only distinct Records
SELECT *
FROM LevelUp L1
WHERE L1.ID = ( SELECT MAX(ID) FROM LevelUp L2
WHERE L2.FirstName = L1.FirstName AND L1.LastName = L2.LastName
AND L1.Address = L2.Address)
GO
|
Delete Duplicate Records from table
DELETE LevelUp
WHERE ID
< ( SELECT MAX(ID) FROM LevelUp E2
WHERE E2.FirstName = LevelUp.FirstName AND E2.LastName = LevelUp.LastName
AND E2.Address = LevelUp.Address)
GO
SELECT *
FROM LevelUp
GO
|
2. Using Common Table Expression
SQL Server 2005 introduced Common Table Expression (CTE) which acts as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Understand CTE With Best Example .
I am using the ROW_NUMBER function to return the sequential number of each row within a partition of a result set which is a grouping based on [FirstName], [LastName], [Address] columns (or columns of the table) and then I am deleting all records except where the sequential number is 1. This means keeping one record from the group and deleting all other similar/duplicate records. This is one of the efficient methods to delete records
WITH JP
AS
(
SELECT ROW_NUMBER() OVER
( PARTITION
BY [FirstName], [LastName] Order by FirstName) As RowNumber,
FirstName ,LastName FROM LevelUp tbl)
DELETE FROM
JP Where RowNumber > 1
GO
SELECT *
FROM LevelUp
GO
|
OUTPUT for both methods
0 comments:
Post a Comment