mysql insert select in same statement

Question

I want to simulate an auto_increment scenario during an insert without the field having the auto_increment property. Here is the scenario sql statement:

insert into acct set id=(select @vid:=max(id)+1); select @vid;

Basically, I want the insert and select done at the same time so I can guarantee the value of vid is unique.

Why don't you use the auto_increment feature MySQL has? dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html — DXL
– DXL, Commented Mar 14, 2011 at 18:55
You're going to have concurrency headaches if you try to roll your own auto_increment functionality. — Joe Stefanelli
– Joe Stefanelli, Commented Mar 14, 2011 at 18:55

Quassnoi · Accepted Answer · 2011-03-14 18:59:15Z

1

If you want the autoincremented value to be shared across transactions with guaranteed uniqueness, you should have a lockable singleton visible to all transactions which would hold the last unique value.

In MyISAM, it is stored in the table's metadata, in InnoDB, in a special memory object populated with MAX(id) on the first insert after the server startup.

Of course you can make your own (say a dedicated table with a single record), but, honestly, I don't see any benefits over the build-in functionality in such a solution.

answered Mar 14, 2011 at 18:59

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

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

Comments

bensiu · Accepted Answer · 2011-03-14 18:54:17Z

0

insert into acct values ( (select max(id)+1 from acct), ... rest_of_vars );

answered Mar 14, 2011 at 18:54

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

2 Comments

Ken Over a year ago

How do I READ this value from the insert? That is, I need to know the value of select max(id)+1 during the insert operation.

bensiu Over a year ago

new - incremented - value will be inserted automatically so knowing value during is not needed. if after is needed <code>(select max(id)+1 from acct)</code>

krtek · Accepted Answer · 2011-03-14 18:56:46Z

0

You can do all this in a transaction to guarantee no one else will access the table :

START TRANSACTION;
insert into acct set id=(select @vid:=max(id)+1);
select @vid;
COMMIT;

answered Mar 14, 2011 at 18:56

krtek

26.7k5 gold badges60 silver badges84 bronze badges

1 Comment

Ken Over a year ago

Can I do this in one SQL statement without transactions? Basically I want to read the value of max(id)+1 during the insert operation.

Collectives™ on Stack Overflow

mysql insert select in same statement

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related