Ruhul Amin Ruhul Amin - 28 days ago 8
SQL Question

Should I use multiple query or functions?

I have database table like:

id name fullname parent_id
-- ---- ----------------- ---------
1 cat1 cat0 > cat1 null
2 cat2 cat0 > cat1 > cat2 null
3 cat3 cat1 > cat3 null


I want to get id of parent category and store into parent_id using sql query.

For example: In row 3, 'cat1' is the parent category of 'cat3', I want to get id of the parent category and store into
parent_id
field.

I splited out the parent cat name using

select id,name,
substring_index(
substring_index(fullname, '>', -2),'>',1)
as parent from categories


From where I can name of parent category.

Expecting output like.

id name fullname parent_id
-- ---- ----------------- ---------
1 cat1 cat0 > cat1 null
2 cat2 cat0 > cat1 > cat2 1
3 cat3 cat1 > cat3 1

Answer

You can get the parent with a simple join:

select c.*, cp.id as parentid
from categories c join
     categories cp
     on c.fullname = concat(cp.fullname, ' > ', c.name);

You can turn this directly into an update:

update categories c join
       categories cp
       on c.fullname = concat(cp.fullname, ' > ', c.name)
    set c.parentid = cp.id;
Comments