MySQL 5.7 - Update parent records with oldest created child

user_145190 0
Viewed 50

My goal is to create an UPDATE statement that updates parent records' default_page_id to be that of the earliest child created.

The parent/child is 2 levels max/deep limited by the UI; the parent_id column denotes the parent of the record, the type value of root further simplifies what the root/parents are.

I have a dbfiddle with a DDL/DML here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=994d9bf3b20b3bbab21547d4334fc0e7

Ultimately my SELECT which I am trying to get right will become my UPDATE is here but uses MIN(id) which wouldn't be feasible as id is a char(36) and thus id does not autoincrement; I have tried to use MIN(created) but am missing the necessary updates to the GROUP BY in order to solve this and would appreciate another eye!

SELECT
    *
FROM
    (
        SELECT
            test_pages.id
        FROM
            test_pages
        WHERE
            test_pages.default_page_id is null
        and test_pages.type = "root"
    ) as a
LEFT JOIN(
    SELECT
        parent_id ,
        MIN(id) AS id
    from
        test_pages
    WHERE
        test_pages.type = "container"
    GROUP BY
        parent_id
) as b ON b.parent_id = a.id
mysql-5.7 mysql sql
1 Answers

SQL (SELECT):

If I understand the issue correctly, the following sql might be helpful:

SELECT
    r.id,
    r.default_page_id,
    r.type,
    r.title,
    c.parent_id AS container_parent_id,
    c.id AS container_id
FROM
    test_pages AS r
    LEFT JOIN (
        SELECT
            parent_id,
            id
        FROM
            test_pages
        WHERE
            type = "container"
            AND created = (
                SELECT
                    MIN(created)
                FROM
                    test_pages AS tp
                WHERE
                    type = "container"
                    AND test_pages.parent_id = tp.parent_id
            )
    ) AS c ON r.id = c.parent_id
WHERE
    type = "root";

SQL (UPDATE):

The update sql is as follows:

UPDATE
    test_pages AS t
SET
    t.default_page_id = (
        SELECT
            id
        FROM
            (
                SELECT
                    parent_id,
                    id
                FROM
                    test_pages
                WHERE
                    type = "container"
                    AND created = (
                        SELECT
                            MIN(created)
                        FROM
                            test_pages AS tp
                        WHERE
                            type = "container"
                            AND test_pages.parent_id = tp.parent_id
                    )
            ) AS c
        WHERE
            t.id = c.parent_id
    );

Output:

After updating, the test_pages looks like the following table:

id version_id parent_id default_page_id type title created modified
jf2039jf932032f 2 abc1212312efff nan container Child Page 2 2022-02-23 23:25:41 nan
7eajiefaeiaeaeleailje 2 nan 8efjaliejfaelfjael root Root Page A nan nan
8efjaliejfaelfjael 2 7eajiefaeiaeaeleailje nan container Child Page Again 1 2022-02-04 23:26:01 nan
abc1212312efff 2 nan ejejejejejej root Root Page nan nan
ejejejejejej 2 abc1212312efff nan container Child Page 1 2022-02-03 23:26:01 nan
fefeee9f9e9fee 2 nan nan root Root Page With No Children nan nan
jklfjealkjlfajfl 2 7eajiefaeiaeaeleailje nan container Child Page Again 2 2022-02-07 23:25:41 nan

Note:

If there are more than one container with different id but with exactly the same created under the same parent container, you might get an error as follows. In this case, you have to write additional code. So be careful please.

Query 1: Subquery returns more than 1 row
user_180598790
Related