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.