CODEWITHSUNDEEP
phpmysql
Bxx
Bxx

Reputation: 1625

MySQL Insert row with column value same as Auto Increment

The id column is auto increment. I want the "rank" column to have the same value as the newly generated auto increment value from the "id" column. (eg. say the next auto increment value is 999... I want the rank to equal this too)

Can I do this in one query? OR do I have to run an additional query to get the auto increment value before

$query = "INSERT INTO $table1(id,name,rank)
VALUES('','bob','999')";

Upvotes: 5

Views: 8367

Answers (2)

Ondřej Šotek
Ondřej Šotek

Reputation: 1812

You can get actual auto_increment value in one insert using this query:

insert into tablename (name,rank) values ( 'bob',(SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tablename'
AND table_schema = DATABASE( ) ) )

See more here - How to get the next auto-increment id in mysql

Upvotes: 5

Marc B
Marc B

Reputation: 360802

last_insert_id() is only set AFTER the insert completes (successfully or not). If you try something like

INSERT INTO yourtable (id, dupe_id) VALUES (null, last_insert_id())

and hoping that dupe_id gets the same ID that'll be assigned to id, then... no, it won't. last_insert_id() will return the ID creatd by whatever insert was run BEFORE this particular statement.

You'll have to do (basically):

INSERT ...;
UPDATE yourtable SET dupe_id=last_insert_id() WHERE id=last_insert_id();

Upvotes: 4

Related Questions