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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

insert into table1 id1  select  max id1  1  from table1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use table alias in subquery   INSERT INTO customers     customer id  firstname  surname   VALUES      SELECT MAX  customer id   FROM customers C   1   jim    sock

[mysql] Insert and set value with max()+1 problems

Examples related to mysql

Examples related to sql

Examples related to insert

Examples related to mysql-error-1093