Glen Swift Glen Swift - 3 months ago 14
SQL Question

Delete duplicates from postgresql database where certain field is null

Consider following table:

+----+-------+--------------+
| id | name | info |
+----+-------+--------------+
| 1 | Chris | NULL |
| 2 | Den | some info |
| 3 | Bob | another info |
| 4 | Bob | NULL |
+----+-------+--------------+


I want to delete all duplicates in field
name
but only those where
info
is
NULL
. For this example I'd like to delete only last Bob (id = 4).

Seems like I can select them with:

select name from mytable where info is null group by name having count(*) > 1;


But I can't check it's all right cause adding fields throws me an error:

select id, name from mytable where info is null group by name having count(*) > 1;



ERROR: column "mytable.id" must appear in the GROUP BY clause or be
used in an aggregate function LINE 1: select id, name from
mytable where info is null group ...


Thanks.

Answer

This should do it:

delete from mytable 
where info is null
and exists (select *
            from mytable t2 
            where t2.name = mytable.name
              and t2.info is not null)