php - MySQL: Add sequence column based on another field

Question

I'm working on some legacy code/database, and need to add a field to the database which will record a sequence number related to that (foreign) id.

Example table data (current):

ID     ACCOUNT     some_other_stuff
1      1           ...
2      1           ...
3      1           ...
4      2           ...
5      2           ...
6      1           ...

I need to add a sequenceid column which increments separately for each account, achieving:

ID     ACCOUNT     SEQ     some_other_stuff
1      1           1       ...
2      1           2       ...
3      1           3       ...
4      2           1       ...
5      2           2       ...
6      1           4       ...

Note that the sequence is related to account.

Is there a way I can achieve this in SQL, or do I resort to a PHP script to do the job for me?

TIA, Kev

Answer 1

Create a trigger:

CREATE TRIGGER trg_mytable_bi
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      DECLARE nseq INT;
      SELECT  COALESCE(MAX(seq), 0) + 1
      INTO    nseq
      FROM    mytable
      WHERE   account = NEW.account;
      SET NEW.seq = nseq;
END;