How to Update If Exists, Insert If Not (Aka "Upsert" or "Merge") in MySQL

How do I update if exists, insert if not (AKA upsert or merge) in MySQL?

Use INSERT ... ON DUPLICATE KEY UPDATE. For example:

INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

SQL - IF EXISTS UPDATE ELSE INSERT

If you have a UNIQUE constraint on (deviceID, datum), then you can use INSERT ... ON DUPLICATE KEY

INSERT INTO distance (deviceID, datum, tavolsag)
SELECT d.deviceID
     , DATE(FROM_UNIXTIME(d.timestamp)) as datum
     , SUM(CASE WHEN d.distanceKM/1000<416 
                THEN d.distanceKM/1000
                ELSE 0 END
       ) AS tavolsag 
  FROM eventdata d
 GROUP BY datum, deviceID
ON DUPLICATE KEY
UPDATE tavolsag = VALUES(tavolsag)

When this statement is executed, this will attempt the insert, and if the inserting would violate a unique key constraint, rather than throw an error, MySQL will execute an UPDATE instead, equivalent to:

UPDATE distance
   SET tavolsag  = ?
 WHERE device_id = ?
   AND datum     = ?

---

If there are multiple unique constraints, then the action depends on which unique constraint is flagged as being violated. The actual update that is performed will be relative to the unique constraint that would have thrown the error.

Update if exist, insert if not exist

Use the INSERT ... ON DUPLICATE KEY UPDATE ... statement.

It will require that you have a UNIQUE or PRIMARY compound index on the date and hotelId columns so that the DUPLICATE KEY condition is triggered.

If exists update else insert in one query

You can utilize ON DUPLICATE KEY UPDATE

INSERT INTO yourtable (`id`, `key`, `value`) VALUES (4, 'something', 200)
ON DUPLICATE KEY UPDATE `value` = 200; 

key column should have UNIQUE index on it

SQLFiddle

update if exists

For this example, let's call your table scores:

Make sure you have a unique index

ALTER TABLE scores ADD UNIQUE INDEX user_category_ndx (user,category);

Now run this to replace:

INSERT INTO scores (user,category,score) VALUES ('username','mycat',127)
ON DUPLICATE KEY UPDATE score = VALUES(score);

If your updating the score means add up scores, then do this:

INSERT INTO scores (user,category,score) VALUES ('username','mycat',127)
ON DUPLICATE KEY UPDATE score = score + VALUES(score);

Give it a try!

UPDATE 2011-08-28 09:00 EDT

If you want to keep just high scores and then add the unique index, here is what you must do:

CREATE TABLE scores_new LIKE scores;
ALTER TABLE scores_new ADD UNIQUE INDEX user_category_ndx (user,category);
INSERT INTO scores_new (user,category,score)
SELECT user,category,MAX(score) FROM scores GROUP BY user,category;
ALTER TABLE scores RENAME scores_old;
ALTER TABLE scores_new RENAME scores;
#
# Drop old table if no longer needed
#
DROP TABLE scores_old;

UPDATE 2011-08-28 10:53 EDT

Run this to keep maximum score:

INSERT INTO scores (user,category,score) VALUES ('username','mycat',127)
ON DUPLICATE KEY UPDATE score = GREATEST(VALUES(score),score);

---

Related Topics