Insert and set value with max()+1 problems

by

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.

More Related Contents:

  1. How to do a batch insert in MySQL
  2. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  3. How to copy a row and insert in same table with a autoincrement field in MySQL?
  4. MySQL Error 1264: out of range value for column
  5. SQL DELETE with JOIN another table for WHERE condition
  6. How do I get the ID of multiple inserted rows in MySQL?
  7. How to insert data to MySQL with auto-incremented column(field)?
  8. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  9. MySQL INSERT INTO … VALUES and SELECT
  10. MySQL error 1241: Operand should contain 1 column(s)
  11. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  12. MySQL optimizing INSERT speed being slowed down because of indices
  13. MySQL: Insert datetime into other datetime field
  14. SQL UPDATE with sub-query that references the same table in MySQL
  15. “Unknown column in ‘field list'”, but column does exist
  16. Select from same table as an Insert or Update
  17. Deleting a row based on the max value
  18. MySQL – Get row number on select
  19. How can I tell when a MySQL table was last updated?
  20. Operand Should Contain 1 Column – MySQL NOT IN
  21. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  22. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  23. Best practices for SQL varchar column length [closed]
  24. How to get the count of each distinct value in a column? [duplicate]
  25. Bash script to insert values in MySQL
  26. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  27. How do I lag columns in MySQL?
  28. How to get multiple records against one record based on relation?
  29. SQL query to check if a name begins and ends with a vowel
  30. How to use a user variables in MySQL LIKE clause?

Leave a Comment