CODEWITHSUNDEEP
mysqlsqlauto-increment
stack
stack

Reputation: 10228

How to insert id's number in the same row?

I'm trying to put the number of id in the neighbor column. Something like this:

+----+-------------------+
| id | identical_with_id |
+----+-------------------+

id is AUTO INCREMENT and all need is getting id's number when inserting. I can do that like this:

INSERT INTO `table_name`
  VALUES( NULL, 
          (SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'database_name' AND
                   TABLE_NAME   = 'table_name')
        );

But I don't know it will work all the time. Maybe between executing that sub-query and inserting that row, a new row (another row) get insert. In this case the number of id will not be the same as neighbor column. Hum? is that possible?

Note: Please don't tell me: "what do you need this for?"

Upvotes: 1

Views: 278

Answers (2)

IMujagic
IMujagic

Reputation: 1259

Usually everyone sometimes need to do such things. And it's best to not ask for reasons :)

I think you are doing it in correct way but you need to check which MySql engine you are using and make sure that inserts are executed sequentially.

Usually insert statement will lock the table and unlock when it's finished. That means your query for retrieving auto increment is pretty much safe.

For MySql engine and related settings this post can help you:

Does a MySQL multi-row insert grab sequential autoincrement IDs?

For InnoDb:

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Upvotes: 2

Ralph Melhem
Ralph Melhem

Reputation: 757

You could use LAST_INSERT_ID() right after your query.

INSERT INTO `table_name` VALUES( NULL, NULL);
SELECT LAST_INSERT_ID() INTO @last_id;
UPDATE `table_name` SET duplicate_id_column = @last_id WHERE id = @last_id;

Upvotes: 1

Related Questions