CODEWITHSUNDEEP
phpsqlmysqlinsert-id
Nathan H
Nathan H

Reputation: 49371

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

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'

Upvotes: 8

Views: 3230

Answers (5)

ChristopheD
ChristopheD

Reputation: 116127

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

Upvotes: 10

ataylor
ataylor

Reputation: 66059

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

Upvotes: 3

violoncello
violoncello

Reputation: 11

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...

Upvotes: 0

Mike Valstar
Mike Valstar

Reputation: 3519

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

Upvotes: 0

NickZoic
NickZoic

Reputation: 7835

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

Upvotes: 0

Related Questions