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..
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.
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.
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.
Source: Stackoverflow.com