SQL – complex SELECT query to UPDATE

SQL noob here. So i have a table with a lot of products. Some of these products are clothing, meaning i have e.g. six different SKU’s for one product due to different sizes. I now want to set the first product variant as the parent for each group. Product IDs and therefore the parent IDs are UUIDs. I managed to write a SELECT query which is taking all the product numbers, finds all groups (by cutting off the last pair of numbers) and assigns all the respective parent (uu)IDs and parent product numbers (for human readable comparison) – it works absolutely fine. But i have no clue on how to convert this rather complex SELECT into an UPDATE. Anyone having ideas? Version is MySQL 8

Table1 looks like this (with all unused columns cut out):

ID        |product_number      |parent_id
-------------------------------------------
[UUID]1-1 |123-456-01          | NULL
[UUID]1-2 |123-456-02          | NULL
[UUID]1-3 |123-456-03          | NULL
[UUID]1-4 |123-456-04          | NULL
[UUID]2-1 |987-65-43-21-01     | NULL
[UUID]2-2 |987-65-43-21-02     | NULL
[UUID]2-3 |987-65-43-21-03     | NULL
[UUID]2-4 |987-65-43-21-04     | NULL

My SELECT query:

SELECT ArticleNumber, ArticleGroup, ParentID, t3.id as ID
FROM (
SELECT t2.product_number as ArticleNumber, GroupTable.GroupNr as ArticleGroup, GroupTable.product_number as ParentID
FROM (
SELECT MIN(result.product_number) as product_number, result.GroupNr
FROM (
        SELECT product_number, 
        SUBSTRING_INDEX(product_number, "-", (LENGTH(product_number) - LENGTH(REPLACE(product_number, "-", "")))) as GroupNr
        FROM table1.product
    ) result
WHERE LENGTH(result.GroupNr) > 0
GROUP BY result.GroupNr
ORDER BY GroupNr
) as GroupTable
JOIN table1.product as t2
ON t2.product_number like concat(GroupTable.GroupNr, '%') AND t2.product_number != GroupTable.product_number
ORDER BY GroupTable.GroupNr 
) as Energija
JOIN table1.product as t3
ON t3.product_number = Energija.ParentID

I want to update the parent_id so that Table1 looks like this:

ID        |product_number      |parent_id
-------------------------------------------
[UUID]1-1 |123-456-01          | NULL
[UUID]1-2 |123-456-02          | [UUID]1-2
[UUID]1-3 |123-456-03          | [UUID]1-2
[UUID]1-4 |123-456-04          | [UUID]1-2
[UUID]2-1 |987-65-43-21-01     | NULL
[UUID]2-2 |987-65-43-21-02     | [UUID]2-2
[UUID]2-3 |987-65-43-21-03     | [UUID]2-2
[UUID]2-4 |987-65-43-21-04     | [UUID]2-2

It works in the SELECT query, i just don’t know how to make an UPDATE out of this.

Sample table with UUIDs switched for string:

CREATE TABLE table1.product (
id varchar(255),
product_number varchar(255),
parent_id varchar(255));

INSERT INTO Table1.product (
id, product_number, parent_id)
VALUES(
'1-1',
'123-456-01',
NULL),
(
'1-2',
'123-456-02',
NULL),
(
'1-3',
'123-456-03',
NULL),
(
'1-4',
'123-456-04',
NULL),
(
'2-1',
'987-65-43-21-01',
NULL),
(
'2-2',
'987-65-43-21-02',
NULL),
(
'2-3',
'987-65-43-21-03',
NULL),
(
'2-4',
'987-65-43-21-04',
NULL);

Answer

You just need to slightly adapt your query and set the parentUuid in the update statement, where the product uuid matches.

In the example code below I adapted your query to get a mapping between the products uuid and the parent uuid. Then I update the table setting the parent_id from the product-table where the products uuid matches the product uuid from the query.

UPDATE table1.product p
SET parent_id = (
    SELECT parentUUID
    FROM (SELECT t3.id as parentUUID, Energija.productuuid as productUuid
          FROM (
                   SELECT t2.id                     as productuuid,
                          t2.product_number         as ArticleNumber,
                          GroupTable.GroupNr        as ArticleGroup,
                          GroupTable.product_number as ParentID
                   FROM (
                            SELECT MIN(result.product_number) as product_number, result.GroupNr
                            FROM (
                                     SELECT product_number,
                                            SUBSTRING_INDEX(product_number, "-",
                                                            (LENGTH(product_number) - LENGTH(REPLACE(product_number, "-", "")))) as GroupNr
                                     FROM table1.product
                                 ) result
                            WHERE LENGTH(result.GroupNr) > 0
                            GROUP BY result.GroupNr
                            ORDER BY GroupNr
                        ) as GroupTable
                            JOIN table1.product as t2
                                 ON t2.product_number like concat(GroupTable.GroupNr, '%') AND
                                    t2.product_number != GroupTable.product_number
                   ORDER BY GroupTable.GroupNr
               ) as Energija
                   JOIN table1.product as t3
                        ON t3.product_number = Energija.ParentID) parentMapping
    where parentMapping.productuuid = p.id);