CODEWITHSUNDEEP
mysqlinsertauto-increment
Nick
Nick

Reputation: 4462

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

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?

Upvotes: 5

Views: 11825

Answers (5)

Omesh
Omesh

Reputation: 29091

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;

Upvotes: 6

Nick
Nick

Reputation: 4462

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.

Upvotes: 3

Andrew
Andrew

Reputation: 1776

You may make second column autoincremented too (with matching AUTOINCREMENT initial value), with or without any keys.

Upvotes: 0

amitchhajer
amitchhajer

Reputation: 12830

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

Upvotes: 2

John Woo
John Woo

Reputation: 263723

You can use mysqli_insert_id if you are using PHP on this.

Example. 

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

/* drop table */
$mysqli->query("DROP TABLE myCity");

/* close connection */
$mysqli->close();
?>

The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

Upvotes: 0

Related Questions