Re: Collecting the primary key using MAX during an insert

[Michael Stassen]

Michael Stassen wrote:
(correcting myself)
<snip>
I'm guessing that the prefix ('FAULT' in this case) varies according to 
the kind of incident, so you want to include it in your table.  The best 
way to do that is to use a separate column (perhaps an ENUM column with 
the possible values).  For example,

  CREATE TABLE yourtable
  (  id INT(7) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     type ENUM('FAULT', 'BUG', 'CRASH', 'REQUEST'),
     other columns...
  );
or, if you want a separate sequence for each type (MyISAM),
  CREATE TABLE yourtable
  (  id INT(7) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
     type ENUM('FAULT', 'BUG', 'CRASH', 'REQUEST'),
     other columns...
     PRIMARY KEY (type, id)
  );
Then you insert the incident with
  INSERT INTO yourtable (type, other_columns)
                 VALUES ('FAULT', other_values);
The ticket number for any row is CONCAT(type, '-', id), so the one we 
just inserted is

  SELECT CONCAT(type, '-', id) AS 'ticket'
  FROM yourtable
  WHERE id = LAST_INSERT_ID();
which works for the first CREATE TABLE above.  For the second, you have 
to take the type into account.  See Shawn's excellent answer for examples.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]