Adding a position column to a child table in MySQL for mapping to a Java List with Hibernate

Question

Hibernate requires a position column on a table that will be mapped to a List. To evolve an existing table, not only does the column need to be added, but it must be numbered correctly for the new mapping to work. For this question I'm assuming children will be numbered starting from 1

The child table is defined:

CREATE TABLE Child (id int NOT NULL auto_increment, parentId int NOT NULL, (primary key (id), KEY index_parentId (parentId));

The position column is added with:

ALTER TABLE Child ADD position int NOT NULL AFTER parentId;

The solution will number the children the fastest way possible.

Answer 1

The best I've come up with is:

CREATE TEMPORARY TABLE tmp.UpdateChild (id int NOT NULL, newPosition int NOT NULL, KEY index_id (id));
INSERT INTO tmp.UpdateChild select id, (select count(*) from Child as otherChild where otherChild.parentId=theChild.parentId and otherChild.id <= theChild.id) from Child as theChild;
UPDATE Child as theChild SET position=(select newPosition from tmp.UpdateChild as updateChild where updateChild.id=theChild.id);
DROP TEMPORARY TABLE tmp.UpdateChild;

I'm using a temporary table because as far as I know it's not possible to select from and update a table in the same query.