0

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.

Improve this question
2
  • Last by id or date? Commented Mar 6, 2019 at 13:21
  • event_id is incremental and data as well. Does not matter. Commented Mar 6, 2019 at 13:25

2 Answers 2

Reset to default
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 )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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';
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.