Reputation: 1294
When inserting a row into a table with an auto increment primary key column, is there a way to get the value that is going to be assigned to that row?
To be clear, I want to use this value as a part of a string for a different column on the same row.
Doing MAX(id) + 1 doesn't seem robust enough. And doing the insert and then an update with LAST_INSERT_ID() is bad because that's 2 separate db calls.
Thanks
EDIT:
What does everyone think of this:
INSERT INTO `mydatabase`.`mytable` (`name`, `description`)
VALUES
(
CONCAT(
'name-',
CAST(
(SELECT
`auto_increment` + 1
FROM
`information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = 'mydatabase'
AND `TABLE_NAME` = 'mytable') AS CHAR
)
),
'description of this thing'
) ;
This way, you should end up with a row with id
of 5 for example, and a name
of "name-5". This is kind of a messy way to go about it, but it should work, no?
Thoughts?
Upvotes: 0
Views: 79
Reputation: 156
You should look in to the SQL command "SHOW TABLE STATUS"
Try it, and google further to find out how to pull the "auto_increment" value out of it.
Upvotes: 1
Reputation: 10637
Ain't that bad. If you're not generating the key externally, then no. You could put a trigger on the table and make that a non auto increment and then generate the key yourself otherwise.
Upvotes: 0
Reputation:
You can't do with auto_increment field. Instead you can manually generate UID
(unique primary key
) and use it for this purpose.
Upvotes: 1