MySQL Update old data after adding new column

MySQL

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 and composite primary key on multiple fields and there the auto-increment would be separate for each PK, which would get me the desired result without triggers, but unfortunately I think I cannot change from InnoDB currently.

Looking for suggestions how to update review_number for all existing records so so these wouldn't be defaulted to 0 after migration scripts finish.

Best Answer

First - your insert trigger may be vulnerable to a race condition, if this is a multi-user system. If two people are inserting a row for the same business_id at the same time, they might be able to get the same MAX(review_number), and thus both set the same review_number. Also, if you enter a new business_id, you may get a NULL value for the review_number (since there are no existing rows, the sub-query would either return NULL or error out). I would recommend setting a unique constraint on business_id and review_number, once you populate the data for the existing rows.

The following should populate all existing rows in review with incremental review_number IDs, starting at 1 for each business ID:

UPDATE `review` r
         INNER JOIN (SELECT id
                           ,@review_num := if(@last_bus_id = business_id, @review_num + 1, 1) as `review_number`
                           ,@last_bus_id := business_id
                       FROM `review`
                      ORDER BY business_id, id
                    ) sq ON (r.id = sq.id)
   SET r.`review_number` := sq.`review_number`
;

Tested via db-fiddle

Related Question