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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Increment a database field by 1

Examples related to mysql

Examples related to insert

Examples related to increment