I have a table that includes versions of my table.
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