8

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'

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Nathan H
  • 48,033
  • 60
  • 165
  • 247

5 Answers5

10

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()
Tom H
  • 46,766
  • 14
  • 87
  • 128
ChristopheD
  • 112,638
  • 29
  • 165
  • 179
  • This could cause a race condition... watch out – Mike Valstar Oct 28 '09 at 23:31
  • 7
    How could it? last_insert_id() is supposed to be connection-specific. To quote the manual: 'Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.' – ChristopheD Oct 28 '09 at 23:33
3

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')
ataylor
  • 64,891
  • 24
  • 161
  • 189
  • Yup, this would be the way to do it in one statement... but it's really not a good idea. I'd definitely do an insert, then update. – Eli Oct 29 '09 at 03:00
  • It's ugly and there's a small race condition between when the inner select completes and the insert runs. – Eli Oct 29 '09 at 03:03
0

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
NickZoic
  • 7,575
  • 3
  • 25
  • 18
0

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

Mike Valstar
  • 3,499
  • 5
  • 24
  • 32
0

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

violoncello
  • 11
  • 2