MySQL auto increment field on UPDATE (initially is NULL)

Question

Let's say I have a table Order with these fields:

• ID (PK and with regular auto_increment)
• payment_date
• invoice_number

Initially Orders are created with payment_date and invoice_number with NULL values, so I could have these rows:

ID | payment_date | invoice_number
1  |     NULL     |     NULL
2  |   03/10/11   |      1
3  |   03/14/11   |      2
4  |     NULL     |     NULL
5  |     NULL     |     NULL

What I wanna do is when I update a row to fill the payment_date I need to update the invoice_number with the first available invoice_number. For instance, if I update the fifth row, the invoice_number would be '3'.

A first approach would be to SELECT the MAX() value for invoice_number and then add 1 to that value, but that would have concurrency problems, right?.

What do you think would be the best solution?

Thanks in advance!

Answer 1

If you want to avoid concurrency problems, you can do this in a transaction or a trigger.

And if you do something like this :

update table
set 
    column = 'something'
    column = (select max(id) + 1 from table)
where id = 123

You won't have concurrency problem either. When you're doing this update, the table is locked, so nobody else can update it while the query is running.