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

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')