Mahmoud ElMansy: knowledge meant to be free

SqlServer-Delete Duplicate Records Rows

This is the second post in removing data from a table or view.

In this post we will discuss how to remove duplicate rows.

In the testing or even production phase of application we fiend that some data repeated or inserted more than once.

This can be very annoying and causes miss behavior of the application so we need a simple and efficient T-SQL Query to Remove this duplicate data .

In the following example we will create a test Table with dummy data to show ho to achieve that.

First : We will create test table Using the following script.

CREATE TABLE [dbo].[Table_Delete](
[ID] [INT] NULL,
[Name] [VARCHAR](128) NULL
) ON [PRIMARY]

GO

Second : We will fill the Test Table with dummy data.
Run the Script

--|--------------------------------------------------------------------------------
--| [Table_Delete] - Backs up all the data from a table into a SQL script.
--|--------------------------------------------------------------------------------
BEGIN TRANSACTION
INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(1, 'Name1');
INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(2, 'Name1');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(3, 'Name1');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(4, 'Name2');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(5, 'Name2');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(6, 'Name3');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(7, 'Name3');

INSERT INTO [Table_Delete]
([ID], [Name])
VALUES
(8, 'Name3');

IF @@ERROR <> 0 ROLLBACK TRANSACTION;
ELSE COMMIT TRANSACTION;
GO
--|--------------------------------------------------------------------------------

 

 

Third : We will make a select statement to select only one of the duplicated rows with out Repeat .

 

SELECT MIN(id) id,Name FROM dbo.Table_Delete
GROUP BY Name

the result .

id Name
1 Name1
4 Name2
6 Name3

Now we can make adelete statement to remove all rows not in this result

BEGIN tran
DELETE FROM dbo.Table_Delete WHERE ID NOT IN(
SELECT MIN(id) FROM dbo.Table_Delete
GROUP BY Name)
commit
SELECT * FROM Table_Delete

And with this query we have achieved or goal in removing duplicate data – Rows
Pretty isn’t it.