Updating ordinal numbers for child records in MySQL table

Question

MySQL database

Each row from article has 0 to 10 rows in price, but ordNum is empty as I added it after few years.

Is there an one-liner to set ordNum so that each article gets values from 1 to n for its prices? For example, article that has 3 prices should have their ordNum set to 1, 2 and 3.

The order of the prices is not important, but it would be an added value if it could be taken into account.

Articles:

CREATE TABLE `article` (
    `id`   INT(11) NOT NULL,
    `name` TEXT    NULL DEFAULT NULL
);

Prices:

CREATE TABLE `price` (
    `id`          INT(11)     NOT NULL,
    `amount`      VARCHAR(24) NULL DEFAULT NULL,
    `article_id`  INT(11)     NULL DEFAULT NULL,
    `currency_id` INT(11)     NULL DEFAULT NULL,
    `ordNum`      INT(11)     NULL DEFAULT NULL
);

NOTE: This question is not duplicate of ROW_NUMBER() in MySQL as it takes two different tables in account comparing to single table in ROW_NUMBER() in MySQL

Answer 1

WITH cte AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY article_id ORDER BY amount) AS ordNum
  FROM price
)
UPDATE price JOIN cte USING (id)
SET price.ordNum = cte.ordNum;

This requires MySQL 8.0 for the CTE and window function.

ORDER BY is just an example; you can choose another order if you want.