Remove duplicate rows in Postgres

Question

I have two tables:

Employee:

ID	Name	Surname
143	Amy	Flowers
245	Natasha	Smith
365	John	Alexander
445	Natasha	Smith
565	Monica	Withhouse
644	Amy	Flowers
1023	Amy	Alexander

And employee_details:

ID	Employee_id	Document_numer
1	644	XXXXXXXXX
2	245	XXXXXX
3	365	XXXXXX

I need to remove duplicate records that are in the Employee table and that are not related to the employee_details table. In the example data, I would like to delete the employee doublet with the id 143 and 445.

And I must admit that I have no idea how to do it.Could you give me a hint? The base is postgres

Are 143 and 445 considered duplicates because of these two conditions. 1) The share the same name and 2) Of the two records sharing the same names these particular records are not in the employee_details table? I'm assuming that's correct since you don't say that 565 should be deleted even though it's not present in the employee_details table; in essence it doesn't match criteria 1. — JNevill
– JNevill, Commented Aug 27, 2021 at 13:54
Yes, I'm sorry. I didn't add important criteria: a duplicate is considered a record with the same 'name' and 'surname' and isn't related to employee_details. — xampo
– xampo, Commented Aug 27, 2021 at 15:20

Eugene · Accepted Answer · 2021-08-27 13:55:00Z

2

Delete from Employee
Where id not in (
   Select Employee_id 
   from employee_details
)
and name in (
  Select name 
  from Employee
  Group by name having count(name) > 1
)

answered Aug 27, 2021 at 13:55

Eugene

1,54310 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xampo Over a year ago

Thanks a lot. I'm sorry because I did not write one thing and didn`t include it in the sample data - a duplicate is considered when the 'name' and 'surname' are the same. Because there may be, for example: Amy Alexander and in your example she will also remove herself.

Eugene Over a year ago

If you need to take into account both name and surname, adjust the subquery to "Select name, surname from Employee Group by name, surname having count(name) > 1"

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-08-27 17:37:29Z

Though the question is already answered I am adding two different answers here using cte.

   create table Employee(ID int, Name varchar(50),  Surname varchar(50));
   insert into Employee values(143, 'Amy',  'Flowers');
   insert into Employee values(245, 'Natasha',  'Smith');
   insert into Employee values(365, 'John', 'Alexander');
   insert into Employee values(445, 'Natasha',  'Smith');
   insert into Employee values(565, 'Monica',   'Withhouse');
   insert into Employee values(644, 'Amy',  'Flowers');
   insert into Employee values(1023,    'Amy',  'Alexander');
   
   create table employee_details ( ID int,  Employee_id int, Document_numer varchar(50));
   insert into employee_details values(1,   644,    'XXXXXXXXX');
   insert into employee_details values(2,   245,    'XXXXXX');
   insert into employee_details values(3,   365,    'XXXXXX');

Delete query 1:

   with duplicate_employees as
   (
     select * , count(id)over(partition by name,surname) duplicate_count from Employee 
   )
   delete from Employee where id in(
   select id from duplicate_employees de
   where duplicate_count >1 
   and not exists
                 (
                   select 1 from employee_details e where e.Employee_id = de.ID
                 )
                 )


   select * from employee

Output:

id	name	surname
245	Natasha	Smith
365	John	Alexander
565	Monica	Withhouse
644	Amy	Flowers
1023	Amy	Alexander

db<>fiddle here

Delete query 2:

 with cte as 
 (
   Select *, count(*)over(partition by name,surname) duplicate_count,
   (case when  exists
    (
      select 1 from employee_details ed where ed.Employee_id = e.ID
    )
  then 1 else 0 end) exist_in_details
  from Employee e 
 )
 delete from Employee where id in (select id from cte where duplicate_count>1 and exist_in_details=0 )



 select * from Employee

Output:

id	name	surname
245	Natasha	Smith
365	John	Alexander
565	Monica	Withhouse
644	Amy	Flowers
1023	Amy	Alexander

db<>fiddle here

Collectives™ on Stack Overflow

Remove duplicate rows in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related