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!

VolkerK · Accepted Answer · 2010-01-31 12:24:04Z

12

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

edited Jan 31, 2010 at 12:24

answered Jan 31, 2010 at 11:54

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tarnfeld Over a year ago

hmm, it doesn't quite work - no errors though. Can i specify the field that it should "ON DUPLICATE" of?

tarnfeld Over a year ago

sorted, i added it into the PRIMARY KEY'S of the table :D

VolkerK Over a year ago

Doesn't have to be the primary key. A unique key suffices (although PRIMARY is always unique)

tarnfeld Over a year ago

yah that, i chose UNIQUE instead of PRIMARY from the dropdown... i guess that did the trick. cheers :D

Collectives™ on Stack Overflow

MySQL Insert on Duplicate Key

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related