3

I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current MySQL tables I have right now.

parent table

id_num  parent_id
------------------
33      parent_1
29      parent_2
46      parent_3
17      parent_4
...     ...

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   NULL
13      child_2   NULL
52      child_3   NULL
76      child_4   NULL
...     ...       ...

And here is what parent_child.csv looks like:

parent_child.csv

parent_id  child_id
--------------------
parent_1   child_3
parent_1   child_4
parent_2   child_1
...         ...

Here is what I'd like the updated child table to be:

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   29
13      child_2   NULL
52      child_3   33
76      child_4   33
...     ...       ...

Assume that not all parent_id and child_id's are in parent_child.csv. So not all child_id's get updated.

How would I go about doing this? Would it require me to create a parent_child table in MySQL?

Here's what I want to do in a nutshell:

for every child_id in parent_child.csv:

  • get child_id's parent_id
  • find parent_id's id_num using parent table
  • replace child_id's foreign key (referred to as parent_id_num, set currently to NULL) with the found parent id's id_num

1 Answer 1

0

You need to bring the CSV table into MySQL, then you can:

update child
left join parent_child on child.child_id = parent_child.child_id
left join parent on parent_child.parent_id = parent_id
set child.parent_id_num = parent.id_num

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .