Add parent_id dynamically based on another field

Question

I have a version table using a field to track order:

id, owner_id, order, version
5, 1, 1, 1.1
6, 1, 4, 1.2
7, 1, 7, 1.2.1
8, 1, 10, 1.3

I want to ALTER TABLE version ADD COLUMN parent_id and have each record reference their parent and then drop the order column. The parent_id is the next lowest in order (which may not be sequential) resulting in:

id, owner_id, parent_id, version
5, 1, NULL, 1.1
6, 1, 5, 1.2
7, 1, 6, 1.2.1
8, 1, 7, 1.3

Can I write a query that will update the parent_id for me? Or must it be done programmatically?

Answer 1

CREATE TEMP TABLE v2
    SELECT * 
    FROM version;

UPDATE version v1 
SET parent_id = (
    SELECT id 
    FROM v2 
    WHERE v2.order < v1.order 
    ORDER BY v2.order DESC LIMIT 1
);