Increment a database field by 1

by

Updating an entry:

A simple increment should do the trick.

UPDATE mytable 
  SET logins = logins + 1 
  WHERE id = 12

Insert new row, or Update if already present:

If you would like to update a previously existing row, or insert it if it doesn’t already exist, you can use the REPLACE syntax or the INSERT...ON DUPLICATE KEY UPDATE option (As Rob Van Dam demonstrated in his answer).

Inserting a new entry:

Or perhaps you’re looking for something like INSERT...MAX(logins)+1? Essentially you’d run a query much like the following – perhaps a bit more complex depending on your specific needs:

INSERT into mytable (logins) 
  SELECT max(logins) + 1 
  FROM mytable

More Related Contents:

  1. MySQL Conditional Insert
  2. Get the new record primary key ID from MySQL insert query?
  3. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  4. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  5. MySql Table Insert if not exist otherwise update
  6. Prevent auto increment on MySQL duplicate insert
  7. MySQL trigger On Insert/Update events
  8. How to copy a row and insert in same table with a autoincrement field in MySQL?
  9. MySQL Error 1264: out of range value for column
  10. BULK INSERT in MYSQL
  11. How do I get the ID of multiple inserted rows in MySQL?
  12. MySQL trigger to update a field to the value of id
  13. Importing a CSV to MySQL with different date format
  14. How to insert data to MySQL with auto-incremented column(field)?
  15. How to insert values in table with foreign key using MySQL?
  16. MySQL INSERT INTO … VALUES and SELECT
  17. MySQL Insert & Joins
  18. How to add a where clause in a MySQL Insert statement?
  19. Reserved word in column name – insert into MySQL [duplicate]
  20. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  21. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  22. Mysql insert into 2 tables
  23. MySQL – insert data from another table merged with constants
  24. Is incrementing a field in MySQL atomic?
  25. Insert data into table with result from another select query
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. MySQL: Insert datetime into other datetime field
  28. “Unknown column in ‘field list'”, but column does exist
  29. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
  30. Insert and set value with max()+1 problems

Leave a Comment