How to insert a line, but in duplicate; Update it instead?


I have a table which contains the items the users of my game owns. If a user buys a new item, it should be inserted, but if he already got it, it should be increased instead.

I understand I can use INSERT ... ON DUPLICATE KEY UPDATE, but I don't understand how in my problem.

The item_id isn't unique, because many players can own the same weapon (ie. a longsword). The user_id isn't unique either, because a player can own many items.

So, my question is how to make the query UPDATE instead of INSERT if a row containing both the user_id and item_id already exists?

That's not what the "ON DUPLICATE KEY UPDATE" will do for you. If it were me, I would attempt the insert. If it failed, check why (you're checking for errors already, right?). If it's because of a duplicate key, do your update. If the error is for some other reason, then handle that appropriately.