MySQL Insert row with column value same as Auto Increment

Question

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')";

Why would you want 2 columns to have the same value? Seems like you should just reference id anywhere you'd need rank.... — kittykittybangbang
– kittykittybangbang, Commented Jul 15, 2015 at 18:16
the only thing i can imagine, is that you want to possibly change rank later on, but id always stays the same. in this case, you could set rank to -1 or NULL or the like in the beginning, and always use the id as rank if you find this value. — hoijui
– hoijui, Commented Jul 15, 2015 at 19:19
rank changes and will ONLY be the same as id on insert. Good point though :) — Bxx
– Bxx, Commented Jul 15, 2015 at 19:52

Community · Accepted Answer · 2017-05-23 12:14:53Z

5

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

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jul 15, 2015 at 18:18

Ondřej Šotek

1,8131 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bxx Over a year ago

From a performance standpoint, is this faster than running 2 separate queries? One to get the AI value, then one to Insert

mikl Over a year ago

@Bxx in MySQL reading a row of information takes less resources than writing a row of information, hence SELECT AUTO_INCREMENT ... should be faster than UPDATE tablename ....

mvorisek Over a year ago

Is this guaranteed to be atomic?

Marc B · Accepted Answer · 2015-07-15 18:15:30Z

4

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();

answered Jul 15, 2015 at 18:15

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

MySQL Insert row with column value same as Auto Increment

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related