20

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

Improve this question

3 Answers 3

Reset to default
13

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your response, but in the end I went for the temporary table solution above.
For anyone having problems with confusing errors, see here about using delimiters in the trigger: stackoverflow.com/questions/5814153/…
10

The question is tagged as "mysql", so yes, MySQL's auto_increment can create groupwise sequential ids.
see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

edit: example php script (using PDO, but it's the same game with the php-mysql module)

$pdo = new PDO('mysql:host=...;dbname=...', '...', '...'); 
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

// example table
$pdo->exec(
  'CREATE TEMPORARY TABLE Foo (
    id int auto_increment,
    account int,
    someotherstuff varchar(32),
    primary key(account,id)
  ) engine=MyIsam'
);
// insert example data
$stmt = $pdo->prepare('INSERT INTO Foo (account,someotherstuff) VALUES (?,?)');
$stmt->execute(array(1, '1a'));
$stmt->execute(array(1, '1b'));
$stmt->execute(array(1, '1c'));
$stmt->execute(array(2, '2a'));
$stmt->execute(array(2, '2b'));
$stmt->execute(array(1, '1d'));
unset($stmt);

// query data
foreach( $pdo->query('SELECT account,id,someotherstuff FROM Foo') as $row ) {
  echo $row['account'], ' ', $row['id'], ' ', $row['someotherstuff'], "\n";
}

prints

1 1 1a
1 2 1b
1 3 1c
2 1 2a
2 2 2b
1 4 1d
Improve this answer

2 Comments

Thanks for the response. Sorry - I should have specified tables are innoDB. I eventually went for the temp tables solution below, but appreciate the input! Cheers!
Nice Solution. This is the correct answer. works great.
9

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.