Insert and set value with max()+1 problems

Question

I am trying to insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with the same customer_id.

With this:

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')

...I keep getting the following error:

#1093 - You can't specify target table 'customers' for update in FROM clause

Also how would I stop 2 different customers being added at the same time and not having the same customer_id?

score 98 · Answer 1 · edited Nov 22 '17 at 18:02

98

You can use the INSERT ... SELECT statement to get the MAX()+1 value and insert at the same time:

INSERT INTO 
customers( customer_id, firstname, surname )
SELECT MAX( customer_id ) + 1, 'jim', 'sock' FROM customers;

^{Note: You need to drop the VALUES from your INSERT and make sure the SELECT selected fields match the INSERT declared fields.}

edited Nov 22 '17 at 18:02

Zuul

15,767
6
58
86

answered Mar 19 '11 at 04:48

EmCo

4,109
2
16
19

6

It may lead to race conditions it these queries are run exactly at the same time. You have to `lock table` before that. – xmedeko Nov 27 '17 at 17:18
@xmedeko I thought 1 query was always atomic, so since there is only 1 `INSERT` here, then it's an atomic operation and race-safe? – Xenos Mar 29 '18 at 15:23
2

Nope, at least no for mysql. This query does select and then insert. They are not atomic. There is a slight chance of race condition. – xmedeko Mar 29 '18 at 17:05
2

Today I learned something new. I thought when performing everything in one query, duplicated queries were impossible, but look, I was wrong. Time to rewrite some things to avoid potential issues in the future. – Fusseldieb Aug 07 '18 at 11:10
I have a similar scenario that I may open a separate SO question for; Selecting the max() is the approach I was looking for too but after reading the comments above, I'm questioning myself. In my instance, I have a `WHERE` clause which is limiting my returned results to a specific subset so I _THINK_ I'm relatively safe from race conditions... right? – HPWD Jun 02 '19 at 16:30

score 29 · Accepted Answer · answered Mar 19 '11 at 04:49

29

Correct, you can not modify and select from the same table in the same query. You would have to perform the above in two separate queries.

The best way is to use a transaction but if your not using innodb tables then next best is locking the tables and then performing your queries. So:

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

Grab the max id and then perform the insert

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')

unlock tables;

answered Mar 19 '11 at 04:49

RDL

7,537
2
26
31

@RDL - I am using MyISAM so your example will work correctly and allow 2 customers to be added at the same time with different customer id's? Thanks – Cozzy Mar 19 '11 at 05:02
1

@Cozzy, Correct. When the table is locked only the current session has access to it. All other requests for that table are placed in a queue until the table is unlocked. Therefore no duplicates will happen from records being added at the same time. – RDL Mar 19 '11 at 05:04
Without a unique key on the customer_id column to validate, there is no guarantee that duplicate values can exist. Additionally, the same max value can be read for the insert statements due to isolation level handling. Why you wouldn't use the native AUTO_INCREMENT? – OMG Ponies Mar 19 '11 at 05:12
@RDL - I get the following error from the 1st query #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@max = SELECT MAX( customer_id ) FROM customers' at line 1 – Cozzy Mar 19 '11 at 05:13
1

@Cozzy: Did you DECLARE the @max variable? Why you want this approach baffles the mind. – OMG Ponies Mar 19 '11 at 05:15
@OMG Ponies - The reason is for the table will hold rows that is the old data before and after modifications. So you can actually see who and what has changed. Baffles my mind to! Might as well make a seperate table for this and just move the data from one table to another before deleting. Thanks for all the help. – Cozzy Mar 19 '11 at 05:31
@Cozzy: I fear for your data model, if you think this is the way to go for an audit table. – OMG Ponies Mar 19 '11 at 06:28
Locking the table is an heavy process... What if I have a `WHERE` in the `MAX` query? Turning `INSERT INTO x (id_autoincremented, num, channel) VALUES (DEFAULT, IFNULL((SELECT MAX(num) FROM x WHERE channel = ?), 0)+1, ?)` to `INSERT INTO x (num, channel) (SELECT IFNULL((SELECT MAX(num) FROM x WHERE channel = ?), 0)+1, ?)` won't lock the table and let other `INSERT` be done for other `channel`, so that's concurrent-safe? – Xenos Mar 29 '18 at 15:22

score 14 · Answer 3 · edited Apr 25 '16 at 06:10

14

Use alias name for the inner query like this

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id )+1 FROM customers cust), 'sharath', 'rock')

edited Apr 25 '16 at 06:10

Musakkhir Sayyed

6,208
13
37
60

answered Apr 25 '16 at 05:37

Sharath Nadig

149
1
2

1

@xmedeko This isn't a duplicate at all. EmCos answer demonstrates replacing VALUES() with SELECT, whereas this answer demonstrates how to nest SELECT statement inside of VALUES() (which folks reading EmCos answer could think is impossible due to his "Note") – Wipqozn Jun 26 '18 at 14:23
@Wipqozn you are right, it's not duplicate, but should be mentioned in the answer what's different. Also, my comments about race conditions apply to this answer, too. – xmedeko Jun 27 '18 at 05:40

score 5 · Answer 4 · edited Apr 18 '12 at 10:51

5

insert into table1(id1) select (max(id1)+1) from table1;

edited Apr 18 '12 at 10:51

antyrat

26,266
9
69
74

answered Apr 18 '12 at 10:44

test

59
1
1

Does not work for MySQL, it does not allow the same table in INSERT and SELECT subquery. – sbrbot Aug 23 '14 at 10:17
1

Worked for me mySQL ver 5.5.41 – ReverseEMF Feb 18 '15 at 08:19

OMG Ponies · Answer 5 · 2011-03-19T05:08:29.527

SELECT MAX(col) +1 is not safe -- it does not ensure that you aren't inserting more than one customer with the same customer_id value, regardless if selecting from the same table or any others. The proper way to ensure a unique integer value is assigned on insertion into your table in MySQL is to use AUTO_INCREMENT. The ANSI standard is to use sequences, but MySQL doesn't support them. An AUTO_INCREMENT column can only be defined in the CREATE TABLE statement:

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
)

That said, this worked fine for me on 5.1.49:

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

INSERT INTO customers VALUES (1, 'a', 'b');

INSERT INTO customers 
SELECT MAX(customer_id) + 1, 'jim', 'sock'
  FROM CUSTOMERS;

How does that not ensure you aren't selecting more than one row? Unless there's a `group by`, then I cannot think of a situation where a query with `max()` will return multiple rows — Brian Leishman, Mar 23 '17 at 17:01
He is not talking about retrieving more than one row with a max(), he is correctly pointing out that by using max(customer_id) + 1 to insert a new customer, you cannot be sure about that id's uniqueness. Two such statements executed at the same time, would result in two different customers having the same customer_id. — jastigarraga, Aug 08 '19 at 07:14

score 4 · Answer 6 · answered Oct 02 '14 at 23:35

4

Use table alias in subquery:

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers C) +1, 'jim', 'sock')

answered Oct 02 '14 at 23:35

Guillaume Renoult

923
7
19

2

I think that would be: `INSERT INTO customers ( customer_id, firstname, surname )` `VALUES ((SELECT MAX( C.customer_id )` `FROM customers C) +1, 'jim', 'sock')` But the following works on my install [mySQL ver 5.5.41]: `INSERT INTO table1(id1) SELECT (max(id1)+1) FROM table1;` – ReverseEMF Feb 18 '15 at 08:34

zipper · Answer 7 · 2018-01-11T09:08:32.680

None of the about answers works for my case. I got the answer from here, and my SQL is:

INSERT INTO product (id, catalog_id, status_id, name, measure_unit_id, description, create_time)
VALUES (
  (SELECT id FROM (SELECT COALESCE(MAX(id),0)+1 AS id FROM product) AS temp),
  (SELECT id FROM product_catalog WHERE name="AppSys1"),
  (SELECT id FROM product_status WHERE name ="active"),
  "prod_name_x",
  (SELECT id FROM measure_unit WHERE name ="unit"),
  "prod_description_y",
  UNIX_TIMESTAMP(NOW())
)

score 3 · Answer 8 · edited Sep 09 '19 at 17:58

3

Your sub-query is just incomplete, that's all. See the query below with my addictions:

INSERT INTO customers ( customer_id, firstname, surname ) 
VALUES ((SELECT MAX( customer_id ) FROM customers) +1), 'jim', 'sock')

edited Sep 09 '19 at 17:58

SOS

4,058
1
8
29

answered Sep 09 '19 at 16:27

saikrishna

39
1

score 0 · Answer 9 · edited Nov 08 '16 at 07:27

0

We declare a variable 'a'

SET **@a** = (SELECT MAX( customer_id ) FROM customers) +1;

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  (**@a**, 'jim', 'sock')

edited Nov 08 '16 at 07:27

Wouter

602
5
14

answered Nov 08 '16 at 06:32

Alex Shel

9
1

1

you should probably add some more annotation to your code. Does this answer both questions asked? Why? – YakovL Nov 08 '16 at 06:56

score 0 · Answer 10 · answered Mar 19 '11 at 04:45

0

You can't do it in a single query, but you could do it within a transaction. Do the initial MAX() select and lock the table, then do the insert. The transaction ensures that nothing will interrupt the two queries, and the lock ensures that nothing else can try doing the same thing elsewhere at the same time.

answered Mar 19 '11 at 04:45

Marc B

340,537
37
382
468

1

Please could you provide a example sql query? When using transaction and lock will it throw any errors if 2 customers are added at the same time or in a nutshell will it just queue them and run one after the other? Thanks – Cozzy Mar 19 '11 at 05:00
1

Actually, you can do the operation in a single statement -- no need for a transaction. And SELECT MAX does not ensure unique values will always be returned -- reading queries are given preference over update queries in default isolation levels on pretty much every database. – OMG Ponies Mar 19 '11 at 05:14
Just a transaction is not enough, you have to lock table first. – xmedeko Nov 27 '17 at 17:19

score 0 · Answer 11 · edited Apr 03 '19 at 05:44

0

This is select come insert sequel.

I am trying to get serial_no maximum +1 value and its giving correct value.

SELECT MAX(serial_no)+1 into @var FROM sample.kettle;
Insert into kettle(serial_no,name,age,salary) values (@var,'aaa',23,2000);

edited Apr 03 '19 at 05:44

Shree

18,997
28
86
133

answered Apr 03 '19 at 05:41

kani sindhu

1
1

Insert and set value with max()+1 problems

11 Answers11

Linked