DELETE DUPLICATE ROWS FROM A TABLE:
At times you will face the issue of duplicate data in the table. As we have to create reports and get data in Relational form, we have to remove the duplicate records and make the records unique.  There are various ways to achieve this and I am listing few of them over here.

You have Table Emp_Details having columns:

Name varchar(20),
Age varchar(20),
Address varchar(20)

I. Using Temporary Table:

Select *  into #temp
from Emp_Details
group by Name, Age, Address
having count(*) =1

Now, Truncate the original table

Truncate table Emp_Details

Again, inserting the records from the temporary table to Emp_Details

insert Emp_Details (Name, Age, Address)
Select Name, Age, Address from #temp

This will insert only distinct records.

ii. Using CTE:

With cte as
( Select  Name, Age, Address, Row_Number() over (partition by name, age, address order by name) as Num
 from Emp_Details
)
Delete from cte
where num > 1

iii. Using Derived Table:

DELETE A
FROM
(
SELECT name,salary, ROW_NUMBER() OVER (PARTITION BY name,salary ORDER BY name) AS rn
FROM Tab
) A
WHERE A.rn > 1



Comments

Post a Comment

Popular posts from this blog

Nested Loop Join There are basically three join mechanisms in SQL Server: i. Nested Loop Join ii. Hash Join iii. Merge Join The Nested Loop Join is chosen by the SQL Server when the tables being used are of the comparable sizes and is been the default join mechanism being used. It is non-blocking,  doesn't require equi-join, data to be sorted and doesn't use tempdb. It is mostly suited for inner joins between tables having relatively smaller inputs and having an index on the joining key. Select a.id, b.id from Table1 a inner Join Table2 b on a.id=b.id If we look at its execution plan, it will be using Nested Loop join. Also, In cases where we are having cross join between two tables mostly Nested Loop join will be used by the SQL Execution plan as it is a product join and produces N*M rows. In cases, if we add certain where clause to the Cross Join query to limit our results, Nested Loop Join might not be used. Basic Example: There is a table with fou...
Read more

Query Execution Plan - Basics

There are two types of Execution plan in SQL Server i.e. Estimated execution plan Actual execution plan Estimated execution plan is generated based on the statistics available for the SQL table columns and indexes. The estimated execution plan may differ from the actual execution plan as the latter is generated when the query is executed and depicts the execution mechanism followed by the server in order to generate the query in the best possible way (least time). Execution plan is the steps taken by the SQL server optimiser to reach the SQL query results in best possible way. We always have questions such as why our query is running slow, how we can reduce the time to execute the query. The query which is to be executed is submitted to the SQL Server, the query optimiser is responsible for parsing the query and generating the execution plan. When the query is submitted to the system, there are many underlying operation that are happening in the backend before the result is...
Read more