MySQL Insert on Duplicate Key.

Question

I am building a rating system, and i want to insert a new row, if the name field does not already contain the name i want to insert, and if it does exist, i want to increase the count field by 1

For example, if i have a row the the name 'Tom' and i try to insert another row with the name 'Tom, then i want to +1 for the field count on the row that already exists.

If a row with the name 'Tom' does not exist, i want to insert a new one and set count to 1.

I know i could do this with about 3 SQL statements and some if statements, but that would slow down the script as 2/3 sql commands are being executed.

Any ideas? Thanks!

Answer 1

see INSERT ... ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

e.g.

INSERT INTO table (name,counter) VALUES ('Bob', 1)
  ON DUPLICATE KEY UPDATE counter=counter+1