MySQL – How to Set a Parent in One-to-Many Relationship

foreign keyinsertMySQL

I have tables as

CREATE TABLE articles
(
id varchar(10),
parent_id varchar(10),
Text text,
FOREIGN KEY(parent_id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB

CREATE TABLE article_map
(
id varchar(10),
unified_id int(11),
FOREIGN KEY(id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id,unified_id)
) ENGINE=InnoDB

For each unified_id, there are several ids. The aim is to save all ids in the table article to avoid being added again, but set one of them as the parent, and the reset as children. In this case, the content (Text) is set to NULL for all children, and any request to a child redirects to the parent.

How can I INSERT from table article_map to table articles to set one parent and the reset children of the assigned parent for each unified_id? There is no preference for choosing the parent.

Best Answer

You could use a CASE an correlated subqueries.

In the subquery select the minimum id for the row's unified_id.

If the row's id is equal to this minimum, parent is null, making the article a parent. If it isn't equal to the minimum it's a child of the article with the minimum id. Set the minimum id as parent then.

INSERT INTO articles
            (id,
             parent_id)
            SELECT amo.id id,
                   CASE
                     WHEN amo.id = (SELECT min(ami.id)
                                           FROM article_map ami
                                           WHERE ami.unified_id = amo.unified_id)
                       THEN null
                     ELSE
                       (SELECT min(ami.id)
                               FROM article_map ami
                               WHERE ami.unified_id = amo.unified_id)
                   END parent
                   FROM article_map amo;

Or you can use a union. First select all parents using the minimum and a GROUP BY unified_id. Unify that with an INNER JOIN of article_map an the parents.

INSERT INTO articles
       SELECT min(am.id) id,
              null
              FROM article_map am
              GROUP BY am.unified_id
       UNION ALL
       SELECT amo.id id,
              p.id parent
              FROM article_map amo
                   INNER JOIN (SELECT min(ami.id) id,
                                      ami.unified_id
                                      FROM article_map ami
                                      GROUP BY ami.unified_id) p
                              ON p.unified_id = amo.unified_id
                                 AND p.id <> amo.id;

(Minimum and maximum (and possibly others) are interchangeable here of course.)

Related Question