Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert and set value with max()+1 problems

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?

like image 864
Cozzy Avatar asked Mar 19 '11 04:03

Cozzy


People also ask

How do you select the maximum 1 value in SQL?

Use the MAX function. the Max-1 value from the records. me know if you have any difficulty. You can use this select from where columnname=(select max(columnname) from where columnname=( select max(columnname) from ));

What does Max () mean in SQL?

The MAX() function returns the largest value of the selected column.

Can we use MAX function in where clause in SQL?

Overview. The MAX() function is used with the WHERE clause to gain further insights from our data. In SQL, the MAX() function computes the highest or maximum value of numeric values in a column.

Can we use Max on a column?

MAX can be used with numeric, character, and datetime columns, but not with bit columns.


Video Answer


1 Answers

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.

like image 77
emco Avatar answered Sep 21 '22 13:09

emco