EN VI

Updating ordinal numbers for child records in MySQL table?

How to Updating ordinal numbers for child records in MySQL table

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
);

Solution:

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.

Tags

Pinned Posts

Top 5 Best High CPM Ads Networks For Publishers In 2023

Tags: ads, Publishers, Ads Networks

HawkHost Coupon 2023 – Hawk Hosting Discount Code Up to 40% Lifetime Update Every Day

Tags: Server, host, cloud

Author

Best Solution

Bot Lv 9

Keyword

Updating ordinal numbers for child records in MySQL table, sql, mysql, foreign-keys, relational-database

Answer