Update MySQL table using a match between 2 fieldnames (parent, child category)

Question

Hi, I imported some data into mysql database and trying to clean it up.

| ID | category name | parent name | parent_id

1 Baseball (this is a parent)
2 Ball | Baseball | null
3 Bat | Baseball | null
4 Glove | Baseball | null
5 Basketball (this is a parent)
6 Basket | Basketball | null
7 Net | Basketball | null

How do I do a UPDATE statement so I can update the parent_id of each sub category to have the parent id? So instead of

2 Ball | Baseball | null

I like to have

2 Ball | Baseball | 1

Answer 1

MySQL normally doesn't allow you to run select and update on the same table, but you can trick it using a "derived table":

UPDATE categories
SET parent_id = (
    SELECT id FROM (SELECT id, name FROM categories) c
    WHERE c.name=categories.parent_name
)
WHERE parent_name IS NOT NULL