SQL to delete duplicate entries based on condition

user_11787068 0
Viewed 54

Having this table: locs

o_id | action                           | second_action                    | user  | entity    
1    | Changed site from '12' to '13'.  | Changed site from '12' to '13'.  | admin | 1111
2    | Changed site from '12' to '13'.  | Changed site from '12' to '13'.  | admin | 1111    
3    | Changed site from '12' to '13'.  | Changed site from '12' to '13'.  | admin | 1111
4    | Changed panel from 'st' to 'bl'. | Changed panel from 'st' to 'bl'. | john  | 2222    
5    | Changed panel from 'st' to 'bl'. | Changed panel from 'st' to 'bl'. | john  | 2222    
6    | Changed panel from 'nr' to 'bl'. | Changed panel from 'nr' to 'bl'. | admin | 3333

I want to delete entries that are duplicated base on the action, second_action and entity column so this will be :

locs

o_id | action                           | second_action                    | user  | entity
1    | Changed site from '12' to '13'.  | Changed site from '12' to '13'.  | admin | 1111
4    | Changed panel from 'st' to 'bl'. | Changed panel from 'st' to 'bl'. | john  | 2222
6    | Changed panel from 'nr' to 'bl'. | Changed panel from 'nr' to 'bl'. | admin | 3333

Because I have a older PostgreSQL I can't use common table expression described here: https://www.sqlservertutorial.net/sql-server-basics/delete-duplicates-sql-server/

So I went with a more classic SQL:

DELETE FROM locs
WHERE o_id NOT IN
   (SELECT * FROM (SELECT MIN(o_id)
   FROM locs
   GROUP BY action, second_action, entity) AS t);

I had to use SELECT * FROM AS t because of some weird SQL error:

[HY000][1093] You can't specify target table 'locs' for update in FROM clause

Is my SQL approach correct ? Is there any better query for this ? I will run that on a bigger data set and I wanted to be sure that will do what I'm expecting.

postgresql sql
3 Answers

You could use exists logic here:

DELETE
FROM locs l1
WHERR EXISTS (SELECT 1 FROM locs l2
              WHERE l2.action = l1.action AND
                    l2.second_action = l1.second_action AND
                    l2.entity = l1.entity AND
                    l2.id < l1.id);

Read in plain English, the above says to delete any record for which we can find a duplicate (as defined by having the same action, second_action, and entity values) with a smaller id value. This logic will spare the duplicate with the lowest id value.

user_18632290

If I understand correctly, just use lag():

select t.*
from (select t.*,
             lag(id) over (partition by action, second_action, entity order by id) as prev_id_all,
             lag(id) over (order by id) as prev_id
      from t
     ) t
where prev_id is distinct from prev_id_all;

Note that the above allows for the the same rows interspersed throughout the data. If you just want the first, use distinct on or aggregation:

select distinct on (action, second_action, entity) t.*
from t
order by action, second_action, entity, id;
user_11440350

If are using older PostgreSQL, then for deleting rows you can try this query. It is faster, because it doesn't see if it matches any other for every single item in the table.

DELETE FROM locs X USING (
   SELECT MIN(o_id)as min_id,action, second_action, entity
   FROM locs
   GROUP BY action, second_action, entity    
       ) Y
      WHERE X.action = Y.action
      AND X.second_action=Y.second_action
      AND X.entity=Y.entity
      AND X.o_id <> Y.min_id
user_139998580
Related