Merging Data From Multiple Tables With Nesting/Loops

Question

Long story short I need to replace a website component with a newer one. Problem is the way the old one stored information in the database is very convoluted.

I have a new empty table (for the new component) with 3 columns:

| field_id | item_id | value |

Each value has a different item_id but field_id is a static number (2). I can populate the table from the old component's table (where x is the data I am unable to get from the old table):

    INSERT INTO new_table (field_id, item_id, value)
    SELECT 2, x , content FROM old_table WHERE condition;

The problem is item_id (x above), the old component has this value stored in a different table but the only thing linking the two values is an asset_id from ANOTHER table. So for each row I somehow need to do the following:

1. Get a value called asset_id from old_table_form,
2. Then in a table old_table_content I need the value item-id from the same row as asset_id,
3. Save this value in new_table as item_id for each row.

I thought maybe nesting SELECT commands could work but I am having trouble even visualizing how it should be, or maybe a loop with pseudo-code something like:

    LOOP
    DECLARE var1
    DECLARE var2
    SELECT FROM old_table_form -> 'asset_id'
    var1 = asset_id
    SELECT FROM old_table_content -> 'item_id' from 'asset_id' row
    var2 = 'item_id'
    save var2 in new table as 'item_id'
    repeat for all rows in table
    END LOOP

Is something like this even possible in MySQL/phpmyadmin? Any ideas/advice would be greatly appreciated.

Answer 1

You should be able to do this with a single command executed as a set. There should be no looping. Your description of the problem isn't all that clear. We don't know keys of the tables that you mentioned and nothing of the tables that you didn't mention. Let us know the structure of the other tables.

This approach is a beginning: Warning! Air Code! Will Not Run!

CREATE TABLE old (
      [field_id] int
    , [item_id] int
    , [value] nvarchar(max)
)
CREATE TABLE new (
      [field_id] int
    , [item_id] int
    , [value] nvarchar(max)
)
INSERT new (
      [field_id] 
    , [item_id]

    , [value]

)
SELECT 
      2 
    , old.[item_id]

    , old_table_content.[value]

FROM old

INNER JOIN old_table_form

    -- be careful with cardinality on these joins
    -- These are only guesses
    ON old.something = old_table_form.something
INNER JOIN old_table_content

    ON old_table_form.something_else = old_table_content.something_else

Answer 2

Well I am not sure if I can say I solved the problem but I did find a work around. There are four tables involved:

1. new_component_table:
| field_id | item_id | value |
     2          x     content

old_component_table:

| form_id | content |

old_form_table:

| form_id | asset_id |

old_source_table:

| item_id | asset_id |

What I need is item_id from old_source_table (the last one) to replace x in new_component table (the first one) but as you can see there is no common column or value so JOIN was not working. Since I also need the 'content' value from old_component_table I decided to add an asset_id and an item_id column to that table:

2. old_component_table now looks like:
| form_id | content | asset_id | item_id |

Then I used this command to populate the asset_id field from the old_form_table:

UPDATE old_component_table t1
INNER JOIN old_form_table t2 
ON t1.form_id = t2.form_id
SET t1.asset_id = t2.asset_id;

Then use the same code to get item_id from old_source_table using asset_id:

UPDATE old_component_table t1
INNER JOIN old_source_table t2 
ON t1.asset_id = t2.asset_id
SET t1.item_id = t2.item_id;

Now with old_component_table having all those values I can simply use:

INSERT INTO new_component_table (field_id, item_id, value)
SELECT 2, item_id, content FROM old_component_table WHERE condition;

There is almost certainly a more elegant way to do it but this did work, the nightmare is finally over.