How to assign the parent product the minimum price of child products?

Question

MySQL

Parent products can have multiple children.
How to assign the minimum price of one of the child products to the parent products with one request.
Simplified description of fields

In the "products" table, the
product_id
parent_id fields
In the "prices" table, the product_id
fields
price_id
price UPDATE #__virtuemart_product_prices AS a JOIN #__virtuemart_products AS b ON a.virtuemart_product_id = b.product_parent_id JOIN #__virtuemart_product_prices AS c ON b.virtuemart_product_id = c.virtuemart_product_id
SET a.product_price = c.product_price
Thanks in advance.

Answer 1

UPDATE `prices` AS `prc`
  JOIN (
    SELECT `prd`.`parent_id` AS `product_id`, MIN(`prc`.`price`) AS `price`
      FROM `products` AS `prd`
      JOIN `prices` AS `prc` ON `prc`.`product_id` = `prd`.`product_id`
      GROUP BY `prd`.`parent_id`
  ) AS `min` ON `min`.`product_id` = `prc`.`product_id`
  SET `prc`.`price` = `min`.`price`

The query updates only one level of nesting, that is, the minimum is considered only for immediate descendants.

Answer 2

UPDATE prices as p set price = (select min(price) from prices ch where ch.parent_id = p.id)
where p.parent_id is null