Increment a database field by 1

Question

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

If you can safely make (firstName, lastName) the PRIMARY KEY or at least put a UNIQUE key on them, then you could do this:

INSERT INTO logins (firstName, lastName, logins) VALUES ('Steve', 'Smith', 1)
ON DUPLICATE KEY UPDATE logins = logins + 1;

If you can't do that, then you'd have to fetch whatever that primary key is first, so I don't think you could achieve what you want in one query.

This is more a footnote to a number of the answers above which suggest the use of ON DUPLICATE KEY UPDATE, BEWARE that this is NOT always replication safe, so if you ever plan on growing beyond a single server, you'll want to avoid this and use two queries, one to verify the existence, and then a second to either UPDATE when a row exists, or INSERT when it does not.

You didn't say what you're trying to do, but you hinted at it well enough in the comments to the other answer. I think you're probably looking for an auto increment column

create table logins (userid int auto_increment primary key, 
  username varchar(30), password varchar(30));

then no special code is needed on insert. Just

insert into logins (username, password) values ('user','pass');

The MySQL API has functions to tell you what userid was created when you execute this statement in client code.

I not expert in MySQL but you probably should look on triggers e.g. BEFORE INSERT. In the trigger you can run select query on your original table and if it found something just update the row 'logins' instead of inserting new values. But all this depends on version of MySQL you running.