Access Auto-Increment Value During INSERT INTO Statement

Question

I am currently using MySQL. I have a table that has an auto_increment 'id' field, and an 'imgname' field containing a string that is the file name of an image.

I need to generate the 'imgname' value using the auto_increment value that is create by an INSERT INTO statement. The problem is, I don't know this value until I can use mysql_insert_id, AFTER the insert query has run. I would like to know if it's possible to access this value DURING the insert query somehow and then use it to generate my string in the query statement.

Thanks in advance.

Joe Stefanelli · Accepted Answer · 2011-08-03 19:49:12Z

1

I would keep the id and imgname independent of each other and combine the two on SELECT when needed. If the need is frequent enough, create a view.

answered Aug 3, 2011 at 19:49

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

This might be the best idea, but I really would like to do what I asked in my question if it is at all possible.

Shi · Accepted Answer · 2011-08-03 19:56:49Z

Have a look at LAST_INSERT_ID() function. If performance is not an issue, INSERT regularly, and then UPDATE using LAST_INSERT_ID(), like:

UPDATE table SET name = CONCAT(name, "-", LAST_INSERT_ID()) WHERE id = LAST_INSERT_ID();

Yes, this is what I am currently doing, but I want to do it with one single query if possible.

Collectives™ on Stack Overflow

Access Auto-Increment Value During INSERT INTO Statement

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related