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

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

PostgreSQL - Working with a virtual/generated column

Image
PostgreSQL - Working with a virtual/generated column To create a table in PostgreSQL with a virtual/generated column derived from JSON data, you can use the jsonb data type along with a generated column. Here is an example: Sample Json: { "firstname":"John", "lastName":"Sri" } Create the table : Define the table with a jsonb column and a generated column that extracts a value from the JSON data. Insert data & Query data : Insert JSON data into the table and Query the table to see the generated column in action. Here is a step-by-step guide: Step 1: Create the Table In this example: id : A serial primary key column. data : A JSONB column to store the JSON data. first_name : A generated column that extracts the first_name from the JSON data. last_name : A generated column that extracts the last_name from the JSON data. Here’s how you might use this table: Step 2: Insert Data & Query Data This would result in the following output: In thi...
Read more

Recursive Query Using CTE in SQL Server

Dear Friends, here i am posting the replacement of cursors using recursive query with the use of CTE. Table Creation Scripts : - CREATE TABLE [dbo].[tb_mlm]( [ID] [int] IDENTITY(10000001,1) NOT NULL, [User_ID] [int] NULL, [PARENT_ID] [int] NULL, [poss] [tinyint] NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(1,null,null) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(2,1,0) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(3,1,1) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(4,2,0) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(5,2,1) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(6,3,0) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(6,3,1) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(7,1,0) INSERT INTO [dbo].[tb_mlm]([User_ID] ,[PARENT_ID],[poss]) VALUES(8,1,0) INSERT INTO [dbo].[tb_mlm]([User...
Read more