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?
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 ));
The MAX() function returns the largest value of the selected column.
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.
MAX can be used with numeric, character, and datetime columns, but not with bit columns.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With