MySQL: Add sequence column based on another field
mysql, php, sequence
Solution
This should work but is probably slow:
CREATE temporary table seq ( id int, seq int);
INSERT INTO seq ( id, seq )
SELECT id,
(SELECT count(*) + 1 FROM test c
WHERE c.id < test.id AND c.account = test.account) as seq
FROM test;
UPDATE test INNER join seq ON test.id = seq.id SET test.seq = seq.seq;
I have called the table 'test'; obviously that needs to be set correctly. You have to use a temporary table because MySQL will not let you use a subselect from the same table you are updating.
Problem
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