mysql - Increment a database field by 1

Question

With MySQL, if I have a field, of say logins, how would I go about updating that field by 1 within a sql command?

I'm trying to create an INSERT query, that creates firstName, lastName and logins. However if the combination of firstName and lastName already exists, increment the logins by 1.

so the table might look like this..

firstName----|----lastName----|----logins

John               Jones             1
Steve              Smith             3

I'm after a command that when run, would either insert a new person (i.e. Tom Rogers) or increment logins if John Jones was the name used..

Answer 1

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