Deleting duplicate records using CTE

Hi Guys, here i m going to explain how you can use CTE to delete duplicate records.

suppose you have a table, like:-

M_Emp
EmpId EmpName
E001GAGAN
E001GAGAN
E001GAGAN
E002AMAN
E002AMAN
E003NAMAN

Now  Execute following query to delete duplicate records

;WITH CteExample(EmpId , RowId)
AS
(
    SELECT EmpId,ROW_NUMBER()OVER(Partition by EMPID ORDER by EMPID) RowId
    FROM M_Emp
)
DELETE FROM CteExample WHERE RowId > 1


This query will delete all the duplicate records.

Comments

Post a Comment

Popular posts from this blog

FastEndpoint vs Minimal APIs vs Controller

Here's a tabular comparison between FastEndpoints, Minimal APIs, and Controllers in the context of ASP.NET Core: Feature/Aspect FastEndpoints Minimal APIs Controllers Design Philosophy Focuses on performance and simplicity by providing a fast, endpoint-centric approach Prioritizes minimalism and simplicity, with an emphasis on lightweight, functional programming Based on the MVC pattern, providing a structured, feature-rich approach Performance Optimized for performance with minimal overhead High performance due to minimal abstractions Slightly more overhead due to MVC and routing abstractions Setup Complexity Requires additional setup and configuration but offers more granular control Minimal setup, quickly defined routes, and handlers Requires more setup and configuration with Controllers, Actions, and Routes Routing Explicit routing defined per endpoint, more control over routes Inline routing with lambda expressions, less verbose Attribute-base...
Read more

Workflow Orchestration Tools (Stateless vs Workflow Core vs Elsa vs Step Functions vs Hangfire)

Here's a tabular comparison of Stateless , Workflow Core , Elsa , Step Functions , and Hangfire in the context of building an orchestrator pipeline in C#: Feature/Aspect Stateless Workflow Core Elsa Step Functions Hangfire Primary Focus State machine library Workflow engine Workflow engine and orchestrator Serverless orchestration of workflows Background job processing Architecture In-memory state machine In-memory and persistent workflows In-memory and persistent workflows Cloud-native (AWS) serverless In-memory and persistent job storage Workflow Model State transitions Steps and transitions Steps, activities, triggers Tasks, states, and transitions Background jobs and recurring jobs Persistence No native persistence (requires custom implementation) Supports multiple persistence providers (e.g., EF Core, MongoDB) Supports multiple persistence providers (e.g., EF Core, MongoDB) Managed by AWS Supports multiple storage options (e.g., SQL ...
Read more

AutoMapper vs FastMapper

Here is a tabular comparison between AutoMapper and FastMapper: Feature/Aspect AutoMapper FastMapper Library Type Object-to-object mapping Object-to-object mapping Performance Slower, especially with large and complex mappings Faster, optimized for performance Setup Complexity Requires configuration for mapping profiles, but highly flexible Minimal setup, focuses on simplicity Custom Mapping Supports custom mapping rules and converters Supports basic custom mapping with less flexibility Inheritance Support Well-supported, can map between base and derived classes Limited inheritance support Null Handling Customizable null handling options Basic null handling Mapping Collections Supports mapping collections with built-in configuration Supports collections, faster due to lightweight processing Flattening and Projection Supports flattening complex objects and nested properties Limited support for flattening Assemblies and Dependencies He...
Read more