How do you remove duplicate records with different status in SQL Server?

In this article, I will explain a scenario where the user has to remove duplicate records with different statuses.

Scenario

Consider below the table with 2 columns (Employee Name and Status)

Now, write a query to fetch a unique Employee Name like this –

If the Employee Name is unique then select it and if there are 2 Employee Names (one is active and another one is inactive) then select the name which is active. Read the Remarks column below.

Employee NameStatusRemarks
Amar1Select this record
Amar0Remove this record because the same name is inactive status
Jack0Select this record as this is the only record
Saga1Select this record as this is the only record with active status
Kaka1Select this record as this is the only record with active status
Tata1Select this record as this is the only record with active status

Solution to remove duplicate record with different status

Let’s write an SQL query to achieve the above output.

Create a temp table using the below script –

declare @tmpTable as table
(
EmployeeName nvarchar(100),
EmployeeCode nvarchar(100),
Status bit
)

Insert some rows in the table –

insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Amar','1002',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Amar','1001',0)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Saga','1003',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Kaka','1004',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Tata','1005',0)

Your select query will give the below result –

EmployeeNameEmployeeCodeStatus
Amar10021
Amar10010
Saga10031
Kaka10041
Tata10050

However, we want below output –

EmployeeNameEmployeeCodeStatus
Amar10021
Saga10031
Kaka10041
Tata10050

Use below SQL query to get above output –

delete from @tmpTable
where EmployeeName in
(select EmployeeName from @tmpTable group by EmployeeName having count(*) > 1)
and Status = 0

Run the above query and you will get desire output –

Hope you like this article.

If it is useful to you, please share this on the social media platforms.

You may read this – SQL Server Interview Questions and Answers for beginners

Please follow and like us:

Leave a Comment