Single query to INSERT with next auto increment

[UnfoX]

Hi all,

 

Lets have a table which has to columns. first column (id) has auto increment property. What I want is, when I insert a new row, I want to duplicate the next auto increment value (next id) to the second column (dID). I want to make it with one query.

 

Table example:

id  -  dID

1    |  1

2    |  2

 

PS: I don't want to use a method like max(id) + 1.. because the next increment value may not be +1 if a row is deleted in between.

 

Any suggestions?

Thanks

[fenway]

LAST_INSERT_ID().... and php mysqli library has a function for this too.

[UnfoX]

LAST_INSERT_ID().... and php mysqli library has a function for this too.

 

Thanks but it's not what I'm looking for. I need next insert id, not the last one If you say that next will be last_insert_id() + 1, I think it will not be correct in all cases.

[fenway]

I see... why?  It's always going to be equal to auto-inc value.

[suresh64633]

Hi,

Try this, you will get next auto increment id as "Auto_increment".

$query= "SHOW TABLE STATUS LIKE TblName";

 

Thx

 

 

[corbin]

Hi,

Try this, you will get next auto increment id as "Auto_increment".

$query= "SHOW TABLE STATUS LIKE TblName";

 

thanks

 

 

 

 

There's a reason it's suggested to never depend on data that's not yet there....

 

 

What if a separate connection to the database makes a query between the time that this query is run and the subsequent insert being ran?  Then you have a collision .

[PFMaBiSmAd]

I want to make it with one query.

You cannot. The actual auto-increment value that is inserted is not available inside the actual query. It is only available after the INSERT query has executed.

 

One could try to use the mysql LAST_INSERT_ID() in your single INSERT query to assign the value to a second field, but you will find that it behaves according to the documentation and it does not return the value that was just inserted until the INSERT query has executed.