How to Increment a MySQL Database Field\'s Value by 1?

Incrementing Database Fields

Problem:

In MySQL, how do you increment a database field, such as "logins," by 1 using an SQL command? Consider a table containing the "firstName," "lastName," and "logins" columns. The objective is to create a query that inserts new individuals or increments existing logins for specific names.

Solution:

Updating an Entry:

To increment an existing logins field, use an SQL update statement:

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

Insert New or Update Duplicate Row:

To update existing names or insert new ones, use either the REPLACE syntax or the INSERT...ON DUPLICATE KEY UPDATE option:

-- REPLACE Syntax
REPLACE INTO mytable (firstName, lastName, logins)
VALUES ('Tom', 'Rogers', 1)

-- INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO mytable (firstName, lastName, logins)
VALUES ('Tom', 'Rogers', 1)
ON DUPLICATE KEY UPDATE logins = logins + 1

Inserting a New Entry:

To assign a new login count when inserting, consider using a subquery:

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

The above is the detailed content of How to Increment a MySQL Database Field\'s Value by 1?. For more information, please follow other related articles on the PHP Chinese website!