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.
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
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.