Question 90705 in PostgreSQL Question posted in
The official documentation can be found here.

How to delete ALL of the duplicate rows in Posgresql ( not leave a single ) – Postgresql

I would like to delete rows from my database that have the same name. I’ve checked Stackoverflow and found something like this:

DELETE
FROM my_table mt1 USING my_table mt2
WHERE mt1.my_name = mt2.my_name AND mt1.unique_id<mt2.unique_id;

This of course works but leaves one row. I have a request:

If there are rows with duplicate rows I have to remove ALL of them (not leave one).

Tags:

2

Answers


  1. 0

    We can use GROUP BY with a HAVING clause as subquery:

    DELETE
FROM my_table 
WHERE my_name IN 
(SELECT
    my_name
FROM
    my_table
GROUP BY
    my_name
HAVING 
    COUNT(*) > 1);

    Try out: db<>fiddle

    Login or Signup to reply.
  2. 0

    See if you can use this as a template.

    drop table if exists #have;

create table #have
(
  ID    [int]
, val   [varchar](10)
)
;

insert into #have
values (1, 'a')
     , (2, 'b')
     , (2, 'b')
     , (3, 'c')

select * from #have;

delete a
from #have a
inner join 
(select id from #have 
 group by id
 having count(*) > 1
) b
 on a.id = b.id
 ;

select * from #have;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search