Single query to insert and get last_id or get existing id?

Databases
1

Hi,

I thought I read a nifty solution from rudy a couple of weeks ago but I can’t seem to find it.

I want to insert a new record to a table and get the last_insert_id but in the same query, I want to get the existing id if the recrd was already there.

here is how I currently insert and get last_insert_id


INSERT into file_sequencing
  ( business_id
  , content_category
  , file_name_in_home_language
  , last_updated
  , last_updated_by
  )
  values ( '123', 'pages', 'Home', CURRENT_TIMESTAMP, ? )

and here is how I get last_insert_id


SELECT LAST_INSERT_ID()

all done in a transaction.

I want to get the file_id for this file_name, when there is a new record created or it already exists.

Can it be done in one query?

2

I assume by transaction, you mean a single PHP statement and not a DB transaction?

You could do that using a stored procedure… or a mysql-i multi-query… but with straight PHP+MySQL, not possible in one statement, as far as I know.

3

bazz, you’re thinking of INSERT with ON DUPLICATE KEY, and of course the ON DUPLICATE KEY requires that you have defined a unique key in the table other than the auto_increment

then getting the id whether the insert succeeded or not will depend on a second query which searches on the unique key

4

R937,

Can you do an ON DUPLICATE KEY SELECT id?

I’ve never seen that trigger used that way.

Would be pretty slick though!!! :smiley:

5

sadly, da manual he say no :smiley:

6

thanks.

on dulpicate key would be ok except I wanted to get the id (or the last_insert_id), as appropriate to what was/was not, entered.

I have done it with two queries now - insert and then queried for the id where conditions are true.

bazz