> Here is a summary of how I have merged hierarchical data structures in the
> past. I start by adding a column or two to my destination data tables for
> each table in the tree I need to reconstruct. The first new column (I
> usually call it something like "old_ID") holds the original PK of the
> source record. The second (if necessary) will hold the original PARENT's
> PK value.
>
> As an example, let's imagine that I need to merge a table that looks like
> {ID, PARENT_ID, <DATA columns>} into a new table with the same structure.
> In this table PARENT_ID points to some other record in the same table. I
> would add my two columns like this
>
> ALTER TABLE new_table add old_ID int, add old_parentid int;
>
> Then, I would need to map the INSERT like this
>
> INSERT new_table (old_ID, old_parentid, <data columns>)
> SELECT old_table id, parent_id, <data columns>;
>
> Then I go back and update the PARENT_ID of the records in new_table with
> the new ID value of their OLD parent records.
>
> UPDATE new_table nt1
> INNER JOIN new_table nt2
> ON nt2.old_ID = nt1.old_parentid
> SET nt1.PARENT_ID = nt2.ID
> WHERE nt1.old_parent_ID is not null;
>
> This re-creates the parent-child relationship that used to exist in
> old_table by filling in the new values for the PARENT_ID that were
> auto-generated when the old records were merged. Repeat this for each
> table in your hierarchy. In this example both parent and child records
> were from the same table but they didn't need to be. nt1 is the alias for
> the "child" table while nt2 represents the "parent" table.
>
> You will need to disable any FK constraints (InnoDB) while you rebuild
> your parent-child relationships but once you have filled in the child's
> parent_id field, you should be able to re-enable those keys and move on to
> the next level. If you get an error, check your data. Working from the
> top down, you should not create too many issues, especially if your data
> was well-organized to start with. Once you have regenerated your
> parent-child links to use the new auto_increment values, you can start
> dropping the old_* columns to recover that space and optimize your tables
> at the same time (thanks to the behavior of the ALTER TABLE).
>
> ALSO, before you start, make a backup of your data (mysqldump works well
> for most people). That way if you hose it up in some major way, you can at
> least get back to where you started without too much pain.
>
> If you have any questions, I will be lurking.... ;-)
Thanks for this solution. Indeed it's much simpler then needing 30 000 user
variables :).
--
<mack />
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
