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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.