MySQL Update old data after adding new column

I have a table called review, which looks like that

| id | business_id|
|----|------------|
| 1  |     B1     |
| 2  |     B1     |
| 3  |     B1     |
| 4  |     B2     |
| 5  |     B2     |
| 6  |     B2     |

To this table I have added a new column called review_number, which represents an incremental sequence separately for each business (referenced via business_id) and this is handled with BEFORE INSERT trigger. This means for each business the review_number sequence would start from 1 and each new record increments it by one. The new column is BIGINT type and for default value is set to 0 for all existing records.

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       0       |
| 2  |     B1     |       0       |
| 3  |     B1     |       0       |
| 4  |     B2     |       0       |
| 5  |     B2     |       0       |
| 6  |     B2     |       0       |

The update trigger is as simple as it can get:

BEFORE INSERT ON review
FOR EACH ROW BEGIN
    SET NEW.`review_number` = (SELECT MAX(review_number) + 1 FROM review WHERE business_id = NEW.business_id);

Problem

My problem and question is, how would I go about updating all the old records after I would update my database where I have live data already. Desired result would be after updating the old data (and when adding new ones after):

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       1       |
| 2  |     B1     |       2       |
| 3  |     B1     |       3       |
| 4  |     B2     |       1       |
| 5  |     B2     |       2       |
| 6  |     B2     |       3       |
| 7  |     B1     |       4       | <--- new record for B1
| 8  |     B2     |       4       | <--- new record for B2

I have tried achieving it with some procedures but haven't found a neat way of helping old records. As much as I know then MyISAM engine supports a way to have multiple auto-increment fields an

You gave away the answer; here are some more details.

• create a TEMPORARY MyISAM table with a 2-column PK, second column being AUTO_INCREMENT

• INSERT INTO myisam_table (business_id, id) SELECT business_id, id ORDER BY business_id, id -- to get it to fill in review_number.

• Do a multi-table UPDATE ... SET your_table.review_number = myisam_table.review_number between the two tables to copy those ids.

StackExchange Database Administrators Q#180120, answer score: 2