Update a field to increment until another field changes

Question

This is my first stack-overflow question so apologies if I do something wrong. I avoided using the term "index" in the question so as not to confuse my question with database indices. This question is about index notation - to specify the position of an element in a series of items.

---

Summary

I have a system which stores documents with their corresponding paragraphs; each document contains many paragraphs. This data is stored in a MySQL database.

The paragraphs table has the following fields:

• Primary key (AUTO INCREMENT)
• Document id (foreign key to the document table)

I wish to add a new field to the paragraphs table set to the index of the given paragraph with respect to the document that it's in. This query will only run once during the migration of adding this field; then I plan to populate it as the paragraphs are inserted.

The first paragraph in each document should have index = 0. The second paragraph in each document should have index = 1.

Paragraphs table

Primary key: 1 Document id: 1 Index: 0

Primary key: 2 Document id: 1 Index: 1

Primary key: 3 Document id: 1 Index: 2

Primary key: 4 Document id: 2 Index: 0

Primary key: 5 Document id: 2 Index: 1

An obvious way to do this is to do it on a per-document basis, but this seems to be inefficient across a large database of documents.

Many thanks for your help!

Answer 1

You can do this using variables (after adding the column, of course):

alter table paragraphs add index int;

set @d := 0;
set @i := 0;

update paragraphs
    set index = (if(@d = document_id, @i := @i + 1,
                    if(@d := document_id, @i := 1, @i := 1)
                   )
                ) - 1  -- to start numbering at 0 rather than 1
    order by document_id, primary_key;

Note: index is a really bad name for a column, because it is a SQL keyword. Name the column something else, such as paragraph_number.