How do I insert the value of an auto-increment ID column into another column on inserting into MySQL table?

Question

The first column in my MySQL table contains an auto increment ID. I would like to enter this value into another column when inserting a new row into the table, i.e. I need to read the value in the ID column and insert it into the next column.

Is it possible to do this, and if so, how?

Solution 1

I'm sure some of the above answers work, but I wasn't able to work out how to implement them. I did however successfully implement a solution using $pdo->lastInsertId(), i.e. after executing my INSERT query I added:

$new_id = $pdo->lastInsertId();

$sth2 = $pdo->prepare("UPDATE `tracks` SET `fav_id`= IF(`fav_id`=0,$new_id,fav_id) WHERE `id`=$new_id");
$sth2->execute();

And this sets the fav_id column of the last inserted row to the same value as the id column for this row, if fav_id has not already been set.

Solution 2

This should work:

INSERT INTO table(id, same_id, col1)
(
 SELECT NULL AS id,
        (SELECT AUTO_INCREMENT
         FROM information_schema.TABLES
         WHERE TABLE_SCHEMA=DATABASE() AND
               TABLE_NAME='table') AS same_id,
        "value" AS col1
);

EDIT: As pointed by Jonathan Swartz it does suffer from race condition. To fix this use LAST_INSERT_ID() to get the last inserted id and update this value in new column:

INSERT INTO table(id, same_id, col1)
(
 SELECT NULL AS id,
        NULL AS same_id,
        "value" AS col1
);

SELECT LAST_INSERT_ID() INTO @var_id;

UPDATE  table
SET     same_id = @var_id
WHERE   id = @var_id;

Solution 3

a simple trigger can do the job.

It will be called on when the row is inserted in table 1

and will take the Id from it and file insert into table 2

check manual

Comments

[Nick]

Yes I am using PHP to handle the MySQL insertion. It's not clear to me from your example where I include mysqli_insert_id. Presumably it is after the INSERT query? I can't tell from your answer which line handles the insertion of the column ID into the column ID2.

[Nick]

I am using $id = mysqli_insert_id($sql); $sql2="UPDATE tracks SET fav_id='$id' WHERE id='$id'"; after the INSERT query, but the fav_id is still set to 0.

[Don Srinath]

You cannot have multiple auto-increment columns for same table in mysql.

[Jonathan Swartz]

Is there a danger of a race condition here - like the auto_increment changing (because of a separate insert) in between the time when it is selected and when the insert is committed?

[Jonathan Swartz]

Confirmed that it does suffer from race condition. You can see this by inserting sleep(3) for value, then doing another insert in a separate mysql shell. You'll get the same value for same_id in two rows.

[Omesh]

@JonathanSwartz yes right. I've edited answer for fixing this issue.