MySql increment order ID relative to account

Question

Considering the table orders:

+-----------+----------------+
| AccountID | AccountOrderID |
+-----------+----------------+
|         1 |           NULL |
|         5 |           NULL |
|         1 |           NULL |
|         6 |           NULL |
|         5 |           NULL |
|         2 |           NULL |
|         6 |           NULL |
|         4 |           NULL |
|         4 |           NULL |
|         5 |           NULL |
+-----------+----------------+

How can I assign an incremental ID to AccountOrderID relatively to Account ID, such that it will result like:

+-----------+----------------+
| AccountID | AccountOrderID |
+-----------+----------------+
|         1 |              1 |
|         5 |              1 |
|         1 |              2 |
|         6 |              1 |
|         5 |              2 |
|         2 |              1 |
|         6 |              2 |
|         4 |              1 |
|         4 |              2 |
|         5 |              3 |
+-----------+----------------+

So far I was using the users with a column called LastOrderID and a trigger to increment it alongside with the AccountOrderID (which works fine):

DELIMITER $$
CREATE TRIGGER `new_order` BEFORE INSERT ON maindb.orders
    FOR EACH ROW BEGIN
        UPDATE users
            SET LastOrderID = LastOrderID + 1
        WHERE AccountID = NEW.AccountID;
        SET NEW.AccountOrderID = (SELECT LastOrderID FROM Credentials WHERE AccountID = NEW.AccountID);
    END;
$$
DELIMITER ;

But I believe that the users table shouldn't be used for this purpose: is there an other way to achive this result?

EDIT

Even if it's exactly what GMB said, here is an answer offering a similar example: https://stackoverflow.com/a/2404587/9373031

score 1 · Accepted Answer · answered Mar 14 '20 at 18:45

You can do this with row_number() if you are running MySQL 8.0. But you need another column to make the sort consistent. Assuming that the primary key of the table is order_id, you can do:

select
    order_id,
    account_id,
    row_number() over(partition by account_id order by order_id) account_order_id
from orders

At first sight, I would not actually recommend storing these numbers, since you then need to maintain that derived information - you can create a view instead. But since you asked for an update query:

update orders o
inner join (
    select 
        order_id, 
        row_number() over(partition by account_id order by order_id) account_order_id
    from orders
) o1 on o1.order_id = o.order_id
set o.account_order_id = o1.account_order_id

Note that this updates the whole table at once (while the original solution has a loop within a stored procedure); there is usually no need for loops in SQL, which is a set-based language.

Works like a charm! I would like to know if it's possible to use a `MAX()` on the `AccountOrderID` for each individual `AccountID` somehow and apply the filter only to those with NULL value, and if it could improve performance(hope it makes sense). Thank you. — DadiBit, Mar 14 '20 at 19:12

MySql increment order ID relative to account

1 Answers1