CODEWITHSUNDEEP
sqlpostgresql
Andrew Spott
Andrew Spott

Reputation: 3627

Delete duplicate record which isn't pointed to by another table

I have tables in my DB:

table_1:
id: primary key (sequential)
A: date
B: uuid

table_2:
table_1_id: foreign key to Table 1

table_1 has duplicate entries in the A and B columns that I would like to delete, but one of those entries is pointed to by the foreign key in table_2. I want to delete the entry that isn't pointed to by said foreign key.

Any recommendations? (I'm trying to add a unique index on table_1 on columns (A,B), but I can't because of these duplicates)

Upvotes: 1

Views: 238

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use not exists:

delete from table_1 t1
    using (select a, b, count(*) as cnt
           from table_1
           group by a, b
          ) tt1
    where tt1.a = t1.a and tt1.b = t1.b and tt1.cnt > 1 and
          not exists (select 1
                      from table_2 t2
                      where t2.table_1_id = t1.id
                     );

This will keep singleton values that have no foreign key reference. Note: If there are duplicates and none are referenced, they will be deleted. That seems to be what you are asking for.

EDIT:

I am guessing that you really want to do the following:

  • Delete all non-referenced rows in table_1
  • EXCEPT for a/b values that have no references. In that case, keep one of them.

If so:

delete from table_1 t1
    from (select tt1.*,
                 count(*) filter (where in_tt2) as num_in_tt2,
                 row_number() over (partition by a, b order by id) as seqnum
          from (select tt1.*, 
                       (exists (select 1 from tt2 where tt2.table_1_id = tt1.id)) as in_tt2
                from table_1 tt1 
               ) tt1
         ) tt1
    where tt1.id = t1.id and
          (not tt1.in_tt2 and num_in_tt2 = 0 and seqnum > 1);

Upvotes: 1

Related Questions