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..

Burdensome

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

Voncile

Also, be sure to add your WHERE clause as appropriate to your application. – Downstate

Oh really!! I wasn't aware you could do that!!! So would this work? INSERT IGNORE mytable (firstName, lastName, logins) VALUES (John, Smith, logins = logins + 1) – Burdensome

@Burdensome No, that's an INSERT, not an UPDATE. If you wish to do an insert, you will need to get the max and add 1 to it. – Voncile

ok, so how do I get around the issue of a record that doesn't already exist in the table? I obviously can't UPDATE a record that doesn't exist :p – Burdensome

@Burdensome Your question asked "about updating that by 1" which caused the confusion. I think you may be looking for another solution, which I have referenced in my updated answer. – Voncile

@Jonathan Sorry for the confusion.. I've update my question, hopefully thats a bit clearer.. – Burdensome

@Voncile According to the document, the REPLACE syntax can't archive the goal: "You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1." – Swipple

Beware that ON DUPLICATE KEY UPDATE is not completely replication safe! – Undry

Isn't there potential for a race condition during the INSERT or UPDATE? – Trifoliate

Answer 2

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.

Timpani

64+ byte primary key isnt a great suggestion – Mapel

Would using a variable work in the place of the '1'? i.e. ON DUPLICATE KEY UPDATE logins = logins + numLogins; (formatted correctly of course) – Burdensome

Beware that ON DUPLICATE KEY UPDATE is not completely replication safe! – Undry

Answer 3

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.

Undry

Answer 4

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.

Determine

Answer 5

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.

Mash