MySQL: use the id of the row being inserted in the insert statement itself

Question

I'm trying to some something like that:

INSERT INTO dir_pictures SET filename=CONCAT(picture_id,'-test');

picture_id is my primary key, auto-increment. Basically I'm trying to put the id of this insert statement, in the statement itself.

I'm sure it can be done with some extra PHP code or using more than one statements, but I was wondering if there is a quick and easy way to do it in one shot.

PS. The statement above always put '0-test'

Answer 1

Insert a record first. Then separate your statements with a semicolon and use LAST_INSERT_ID() to fetch the newly inserted autoincrement id. Execute in one go.

insert into dir_pictures .... ; 
update dir_pictures set filename=CONCAT(LAST_INSERT_ID(),'-test') where id = LAST_INSERT_ID()

Answer 2

Well, you don't get to know the row id until the insert has completed ... so, I don't think that's ever going to work!

Why not just calculate the filename when you retrieve the row instead?

INSERT INTO dir_pictures (file_extension) VALUES ('-data')

SELECT picture_id, CONCAT(picture_id, file_extension) as filename
FROM dir_pictures

Answer 3

This is not really possible, it would probably be in your best interest to simply concatinate it on the way out instead of the way in.

SELECT CONCAT(id, '-text') as filename FROM dir_pictures

Answer 4

If you really want to do it in one SQL statement, you could try a hack like:

INSERT INTO dir_pictures SET filename=CONCAT((select max(picture_id)+1 from dir_pictures),'-test');

Beware of race conditions if this is a multi-threaded app...

Also, I doubt it'd work on an empty table...

Answer 5

Just select the current auto_increment value for the table form the information_schema as part of your insert:

INSERT INTO dir_pictures SET filename=CONCAT((SELECT auto_increment FROM
information_schema.tables WHERE table_name='dir_pictures'), '-test')