Add sequence field to child table rows

I recently got a request from a customer to add the possibility to reorder invoice rows manually. In order to achieve this on the database side you need to add a new column which holds the sequence number of each row. It is easy to add this column with ALTER TABLE statement. But how do you assign a value for each existing row?

This is a simplified version of the invoice_rows table:

SELECT * FROM invoice_row;

+------+------------+-----------+
| id   | invoice_id | product   |
+------+------------+-----------+
|    1 |          1 | Pencil    |
|    2 |          1 | Book      |
|    3 |          2 | Coffee    |
|    4 |          2 | Sandwitch |
|    5 |          2 | Newspaper |
+------+------------+-----------+

Now I add the column for the sequence number:

ALTER TABLE invoice_row ADD COLUMN sequence SMALLINT NOT NULL DEFAULT 0;

This will result in all rows having sequence value 0. Now I want to have a growing sequence number for rows of each invoice so that the end result would be this:

SELECT * FROM invoice_row;

+------+------------+-----------+----------+
| id   | invoice_id | product   | sequence |
+------+------------+-----------+----------+
|    1 |          1 | Pencil    |        0 |
|    2 |          1 | Book      |        1 |
|    3 |          2 | Coffee    |        0 |
|    4 |          2 | Sandwitch |        1 |
|    5 |          2 | Newspaper |        2 |
+------+------------+-----------+----------+

Is it possible to achieve this with a single update statement? I didn’t believe so initially but after some looking around found a very neat solution that works on MySQL.

MySQL supports variables in queries which you can change for each row. Here is an example:

SELECT 
  @sequence := IF(@invoice = invoice_id, @sequence + 1, 0) sequence, 
  @invoice := invoice_id invoice_id
FROM 
  (SELECT @invoice := NULL, @sequence := 0) vars 
  JOIN invoice_row
ORDER BY 
  invoice_id, id;

+----------+------------+
| sequence | invoice_id |
+----------+------------+
|        0 |          1 |
|        1 |          1 |
|        0 |          2 |
|        1 |          2 |
|        2 |          2 |
+----------+------------+

Start reading it from the FROM section: we set two variables to initial values and join them with the table that we are actually querying.

Then look at the SELECT section: for each row we assign a new value to both variables - the @sequence value is either incremented by one (if the invoice_id is the same as previously) or reset to zero (if we have got a new invoice_id). And @invoice get always the current invoice_id value.

The ORDER BY is important because you want all rows of the same invoice to come together in the result. Otherwise the IF() for new @sequence value would work incorrectly.

Once you have this, it can be used in an UPDATE statement to change all rows:

UPDATE invoice_row ir,
  (SELECT 
     @sequence := IF(@invoice = invoice_id, @sequence + 1, 0) sequence, 
     id, 
     @invoice := invoice_id
   FROM 
     (SELECT @invoice := NULL, @sequence := 0) vars 
     JOIN invoice_row
   ORDER BY invoice_id, id) t
SET ir.sequence=t.sequence WHERE ir.id=t.id;

Notice that I added id to the selected column list as this is how you join the result with the actual updated table.

And now I have proper sequence value for each row!

Being able to do this with a single SQL statement means that I can easily add it as a database migration and it will be executed correctly in all environments.