Problem with MySQL Insert Max()+1

Problem with MySql INSERT MAX()+1

You can use something like this:

INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';

But such query can lead to a race condition. Make sure you are in a transaction and you lock the users table before running it. Otherwise you might end up with two Bobs with the same number.

Insert and set value with max()+1 problems

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;

Trouble with MAX(col)+1 INSERT into same MySQL table

You want to use INSERT INTO .... SELECT FROM instead of INSERT INTO...VALUES():

INSERT INTO invoices (invoiceid)
SELECT MAX(invoiceid)+1 
FROM invoices

My question for you would be why are you not use an AUTO INCREMENT field to generate the invoiceid value? That is what it is for, then you will not have to create this when inserting data.

SQL Insert with max ID + 1 (In-Depth)

If an auto incremented column is not an option for a reason, try

INSERT INTO customers(customer_id, firstname, surname)
SELECT MAX(customer_id) over()
   + row_number() over(order by customer_id), firstname, 'shoe' 
FROM customers;

This is subject to race condition, you know.

EDIT

Another option just find max first

SET @m = (SELECT MAX(customer_id) FROM customers);
INSERT INTO customers(customer_id, firstname, surname)
SELECT @m
   + row_number() over(order by customer_id), firstname, 'tie' 
FROM customers
WHERE surname = 'sock';

db<>fiddle

insert into select max+1 in mysql

Check the row_number() function, it might help you

insert into tab_a (mid,itemcode,seq)
select B.mid, 
       B.itemcode, 
       ifnull(max(A.seq), 0) + row_number() over(partition by mid)
  from tab_a A
  right join tab_b B 
    on A.mid = B.mid 
 group by B.mid, B.itemcode;

see db_fiddle

MySQL: Insert new record using MAX value of a column in the same table

If you want to use a subquery in an Insert you don't have to use VALUES

Try this query:

INSERT INTO store_item_photo (id_item, position)
  SELECT
    3 AS id_item,
    MAX(position) + 1
  FROM store_item_photo 
  WHERE id_item = 3;

insert into table select max(column_name)+1

Try this:

  INSERT INTO posts (post_user_id,gen_id) 
  SELECT 1, MAX(gen_id)+1 FROM posts;

INSERT INTO t(x) .. SELECT MAX(t.x) + 1 .. sometimes returns DUPLICATE KEY

Use SERIAL instead, which is something like AUTOINCREMENT.

CREATE TABLE table_name(
    id SERIAL
);

Never take COUNT+1 as two users at the same time could get the same ID, or mixed-up ID (if you use that ID further).

For good order: in a programming language like java, after inserting (without the ID) you can use getGeneratedKeys to obtain the generated primary key.

---

Related Topics