title: SQL - complex SELECT query to UPDATE
body: <p>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</p>
  <p>Table1 looks like this (with all unused columns cut out):</p>
  <pre><code>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
  </code></pre>
  <p>My SELECT query:</p>
  <pre><code>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, &quot;-&quot;, (LENGTH(product_number) - LENGTH(REPLACE(product_number, &quot;-&quot;, &quot;&quot;)))) as GroupNr
          FROM table1.product
      ) result
  WHERE LENGTH(result.GroupNr) &gt; 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
  </code></pre>
  <p>I want to update the parent_id so that Table1 looks like this:</p>
  <pre><code>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
  </code></pre>
  <p>It works in the SELECT query, i just don't know how to make an UPDATE out of this.</p>
  <p>Sample table with UUIDs switched for string:</p>
  <pre><code>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);
  </code></pre>