How to insert a row, but on duplicate; update it instead?

Question

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?

Answer 1

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.

Answer 2

You do want ON DUPLICATE KEY UPDATE. It looks for the Primary Key of the table, and if it exists, updates all the other rows.

So your table has a primary key of (userid, itemid) and the following values:

userid   itemid   strength
4        5        6

And you want to bump it to strength=9, use this:

INSERT INTO table ON DUPLICATE KEY UPDATE VALUES(4,5,9)

It will insert a row with 4,5,9 if it doesn't exist, and will update strength to 9 on the row with primary key (4,5) if it does exist. It won't update any other rows (e.g. rows with userid4 but itemid 10 or itemid 5 but userid 70) because they don't match the whole PK.

Answer 3

You could do something like the following (assumes your user id is in the variable UserID and item ID is in item_ID) :

  SELECT
    @rowCount := COUNT(*)
  FROM
    table
  WHERE
     user_id = UserID
     AND item_id = ItemID;


   IF (@rowCount > 0) THEN

   ... do update

   ELSE
    ... do insert

   END IF;

Answer 4

Can you do something like

UPDATE TABLE
set COL = 'BLAH BLAH'
where ITEM_ID = @itemid AND USER_ID = @userid

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO TABLE (col...)
    VALUES (vals...
END