How to delete duplicated data by column form database

Question

I have a table that has multiple duplicated rows by one column. So I want to clear duplicated datas.

Events Table:

event_id,   event_type,     data_id,    data    date
    1       insert              1       x       06.03.2019 13:04
    2       update              1       x1      06.03.2019 13:05
    3       update              1       x11     06.03.2019 13:06
    4       insert              2       y       06.03.2019 13:07
    5       update              1       x111    06.03.2019 13:08
    6       delete              1       x111    06.03.2019 13:09        
    7       update              2       y1      06.03.2019 13:10
    8       update              2       y11     06.03.2019 13:11
    9       update              2       y11     06.03.2019 13:12

Each data id has 1 insert, N update and 1 delete event rows in table. So I want to delete N-1 update events but last event will not be deleted. For example in this table, for data_id=1 update events are 2,3,5. I want to delete 2 and 3 but not 5. Because 5 is last update.

event_id is incremental and data as well. Does not matter. – barteloma Mar 06 '19 at 13:25 — barteloma, Mar 06 '19 at 13:25

score 0 · Answer 1 · answered Mar 06 '19 at 13:25

I would write this as an exists:

DELETE
FROM your_table t1
WHERE EXISTS (SELECT 1 FROM your_table t2
              WHERE t1.data_id = t2.data_id AND
                    t2.event_id < t1.event_id AND
                    t2.event_type = 'update') AND
      t1.event_type = 'update';

score 0 · Accepted Answer · answered Mar 06 '19 at 13:26

0

With EXISTS:

delete from tablename t
where
  event_type = 'update'
  and exists (
    select 1 from tablename 
    where 
      data_id = t.data_id 
      and
      event_type = 'update'
      and 
      event_id > t.event_id
  )

answered Mar 06 '19 at 13:26

forpas

145,388
9
31
69

How to delete duplicated data by column form database

2 Answers2