I have a table that includes versions of my table.
x
version_id transaction entity_id date
--------------------------------------------------------------
1 insert 1 11.11.2019 13:15:04
2 update 1 11.11.2019 13:16:04
3 insert 8 11.11.2019 13:17:04
4 update 1 11.11.2019 13:18:04
5 update 8 11.11.2019 13:19:04
6 update 1 11.11.2019 13:20:04
7 update 1 11.11.2019 13:21:04
8 delete 1 11.11.2019 13:22:04
For example entity id 1, I need 1,7,8 version numbers. 2,4,6 version numbers are not needed, so I can delete them. Actually I need last insert, update, delete transaction version numbers for an entity. The others will delete.
Can I remove all unnecessary update ids in this way?
Advertisement
Answer
If “last” is defined by the value of date
, you can use this query to delete earlier versions:
DELETE FROM versions v1
WHERE date < (SELECT MAX(date)
FROM versions v2
WHERE v2.entity_id = v1.entity_id
AND v2.transaction = v1.transaction)
Output (from SELECT *
after DELETE
) for your sample data:
version_id transaction entity_id date
1 insert 1 2019-11-11T13:15:04.000Z
3 insert 8 2019-11-11T13:17:04.000Z
5 update 8 2019-11-11T13:19:04.000Z
7 update 1 2019-11-11T13:21:04.000Z
8 delete 1 2019-11-11T13:22:04.000Z