Dan Rossi wrote:
Hi there, I was wondering how its possible to get the MAX of a primary key of a table during an insert. I basically want to create a ticket number, but use the primary key as part of the ticket number ie FAULT-0000001 or FAULT-00000002 . I tried during a sub query on an insert but obviouslly not working :|
Let me know.
Well, you can get the maximum id using the MAX() function,
SELECT MAX(id) FROM yourtable;
but you really don't want to do that, as it's not safe. There's no guarantee the max id is the row you just inserted, since someone else may have also inserted a row. Instead, use LAST_INSERT_ID() (<http://dev.mysql.com/doc/mysql/en/information-functions.html>). It's connection-specific, thus guaranteed to be the row you just inserted.
Are you trying to store the ticket number in the table, or just display it in your output? If the latter, you could do something like this:
INSERT INTO yourtable (list of columns) VALUES (list of values);
SELECT CONCAT('FAULT-', LAST_INSERT_ID()) AS 'ticket number';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();
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
