how to delete duplicate records in sql server, sql delete duplicate rows but keep one, how to delete duplicate rows in oracle, delete duplicate rows in sql, how to delete duplicate records in mysql, delete duplicate rows in sql w3schools, to remove duplicate rows in sql select statement, sql remove duplicates based on two columns, delete duplicate records in sql server using row_number, how to delete duplicate rows in sql, delete duplicate rows in sql w3schools, sql delete duplicate rows but keep one, how to delete duplicate rows in oracle , sql remove duplicates from (select), how to delete duplicate rows in sql using rowid, how to delete duplicate records in mysql
In my previous article, I explained how to remove duplicate records from a table using UNION and EXCEPT Operator in SQL Server with an example.
What is CTE(Common Table Expression) in SQL Server?
In SQL Server CTE(Common Table Expression) is introduced in SQL Server 2005, and is used to create a recursive query. This is a named temporary result set that can be referenced within a choose, Insert, updatingor Clear statement, as well as preferably used as a sub/view query.
In SQL server view it is a virtual table based on the result set of SQL statement and a sub query is a query within the query and we can also say it is an inner/nested query.
syntax
;With Alias_Tablename (column1,column2...,columnsN) AS ( Your Query )
Common Table Expression (CTE) types in SQL Server
In SQL Server Common Table Expressions (CTE) have two different types recursive and non-recursive. Where a recursive CTE can be explained in three different parts anchor query, separatorand Recursive query.
What is an anchor query?
Anchor Query will provide the base data for the CTE and this is the first statement that is executed.
What is a separator?
This is the middle part where we usually use an BesidesUNION, UNION ALL and several other operators.
What is a recursive query?
A recursive query is the Common Table Expression (CTE) query that refers to that CTE by recursion, which is the main part of a recursive CTE.
demand
- Create a temp with some dummy records.
- Add duplicate records in the created temporary table.
- Remove duplicate records from the table created using Common Table Expressions (CTE).
Application
As required first, we will create/declare a temporary table named “tblEmployees” in SQL Server.
DECLARE @tblEmployees AS TABLE ( EmpId int NOT NULL PRIMARY KEY, CompanyId int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Designation varchar(50) NOT NULL, Country varchar(50) NOT NULL, Date_Of_Birth DATETIME NOT NULL )
Now, let’s put some dummy duplicate records on the table for demonstration purposes.
INSERT INTO @tblEmployees VALUES (1,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000') INSERT INTO @tblEmployees VALUES (2,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000') INSERT INTO @tblEmployees VALUES (3,10798, 'Hiren', 'Dobariya', 'Sr.Software Engineer','India','1996-03-10 00:00:00.000') INSERT INTO @tblEmployees VALUES (4,10798, 'Ronak', 'Rabadiya', 'Sr.Software Engineer','India','1993-04-09 00:00:00.000') INSERT INTO @tblEmployees VALUES (5,10798, 'Hiren', 'Dobariya', 'Sr.Software Engineer','India','1996-03-10 00:00:00.000') INSERT INTO @tblEmployees VALUES (6,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000') INSERT INTO @tblEmployees VALUES (7,10798, 'Sneha', 'Patel', 'Support Executive','India','1995-02-12 00:00:00.000') INSERT INTO @tblEmployees VALUES (8,10798, 'Sheetal', 'Patel', 'Web Designer','India','1995-02-12 00:00:00.000') INSERT INTO @tblEmployees VALUES (9,10798, 'Ronak', 'Rabadiya', 'Sr.Software Engineer','India','1993-04-09 00:00:00.000') INSERT INTO @tblEmployees VALUES (10,10798, 'Ronak', 'Patel', 'Sr.Software Engineer','India','1995-11-12 00:00:00.000')
If you run the select statement and retrieve the result set from the @tblworkers So you discovered that there are duplicates in @tblworkers Table.
SELECT * FROM @tblEmployees ORDER BY EmpId
Results set
| Duplicate SQL Server records |
As per our requirement, we will remove the duplicate entries/records from the @tblworkers A table with a CTE (Common Table Expression).
;WITH MyEmployees (FirstName,DuplicateCount) AS ( -- Generate RowNumber based on Employee FirstName, LastName Wise SELECT FirstName,ROW_NUMBER() OVER(PARTITION by FirstName, LastName ORDER BY EmpId) AS DuplicateCount FROM @tblEmployees ) --Remove Duplicate Records From MyEmployees (Common Table Expressions) DELETE FROM MyEmployees WHERE DuplicateCount > 1
If you analyze the SQL statement above, I have declared one common table expression (CTE) with the name “my employees”. According to the CTE syntax, I wrote a query where I created a row number partition by employee first and last name order by employee ID and gave the row number column an alias as DuplicateCount.
Finally, I deleted these rows from the CTE table “my employees“Where DuplicateCount is greater than 1.
Now, if you run the select statement and retrieve the result set from the @tblworkers In the table, you will not find duplicates in @tblworkers table and all duplicate records/rows will be removed from your base table @tblworkers.
SELECT * FROM @tblEmployees ORDER BY EmpId
Results set
 |
| SQL Server Remove duplicate records |
The advantage of CTE (common table expression)
-
In SQL Server Common Table Expression(CTE) enhances your Code readings.
-
Common Table Expression (CTE) provides Recursive programming.
-
CTE produces the Maintenance of complex queries easy.
-
It makes code Easier maintenance.
-
It can be used in stored procedures, functions, Operators as well as even Views also.
-
The CTE can be used as a Table or as a View and we can perform SELECT, INSERT, UPDATE or DELETE operations on the table.
Summary
SQL Server CTE(Common Table Expression) creates a recursive query. It is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE or DELETE statement, it is also preferred to be used as a Subquery/View and it improves code readability, maintainability of complex queries, code maintainability and provides recursive programming.
