158

Is there an easy way to INSERT a row when it does not exist, or to UPDATE if it exists, using one MySQL query?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
blub
  • 2,146
  • 4
  • 20
  • 19

2 Answers2

201

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
chaos
  • 122,029
  • 33
  • 303
  • 309
  • Wow, that's awesome. Do other RDMSs have this feature? – Brian Ramsay Aug 02 '09 at 13:38
  • 11
    Yeah, I believe SQL Server's equivalent is called `MERGE`. In general, the concept is often referred to as `"UPSERT"`. – chaos Aug 02 '09 at 13:40
  • But this doesn't work when there is no prim.key present. My table looks like this: geb | topic | visited I want to insert INSERT INTO table SET visited=NOW(), geb=1, topic=1 But if there is already a combination of geb=1 && topic=1 then it should update the row instead of insert a new one. – blub Aug 02 '09 at 13:40
  • 3
    @blub: If you create a unique key on `geb` and `topic` it will work (`ALTER TABLE table ADD UNIQUE geb_by_topic (geb, topic)`). – chaos Aug 02 '09 at 13:43
  • I didn't enter a prim.key because i then had to use a new column cause geb and topic are both not unique. I didn't know it was possible to make a key pairing two columns!! Thanx chaos. – blub Aug 02 '09 at 13:56
  • 1
    @Brian: Oracle's equivalent is also called `MERGE` but I'm not sure if its syntax is identical to SQL Server's. – Ken Keenan Aug 02 '09 at 14:20
  • @iAn: That isn't really all it does. If you check for existence using a `SELECT`, then perform an `INSERT`, you have concurrency issues that don't exist if you're using an `ON DUPLICATE KEY UPDATE`. – chaos Aug 02 '09 at 14:22
  • As must of the time `on duplicate` is hit, is this codes performance good? is there a better solution? – Yasser Souri Jun 13 '11 at 20:00
  • I take it of 'thing_id' is set to autoincrement (and is the primary key) and you pass it '0' the result will be an insert at the next value for 'thing_id' ? – Brooks May 17 '12 at 16:45
  • 1
    @Brooks: If you pass it a 0, it will actually use 0 as the value. So don't do that. Don't pass it anything, or pass it a `NULL`, to allow the `auto_increment` behavior to work (which otherwise, yes, works as you presume; see http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html). – chaos May 17 '12 at 16:56
  • 1
    How do you do this if there are multiple unique indexes? I only want to update a record if the record already existing has 2 properties that are equal, not one or the other. – CMCDragonkai Nov 07 '13 at 21:42
  • @CMCDragonkai: The `ON DUPLICATE KEY` part of the mechanism means that it only goes to an `UPDATE` if the `INSERT` attempt generates a duplicate in a unique key. If that wouldn't occur in your case, then you can't use this mechanism to address it. – chaos Nov 08 '13 at 15:42
  • For those who are wondering, this example WILL update times_used for the discovered duplicate row. – Ne Ma Feb 25 '14 at 17:06
  • I am already using this and I want to extend this question. Is there any way to get the effected(updated/inserted) ID form this method – Ruwantha Apr 07 '14 at 17:45
  • @Ruwantha: The docs contain an example for doing this: https://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html – chaos Apr 07 '14 at 17:50
  • @chaos: It is very helpful , How ever in my case id is not a increment one. I guess there is no solution for that. – Ruwantha Apr 08 '14 at 07:40
  • 3
    Thank you, and you can use `VALUES(col)` to get the value from the insert argument in case of duplicate. Eg: `ON DUPLICATE UPDATE b = VALUES(b), c = VALUES(c)` – gerrytan May 20 '14 at 01:51
  • @chaos and others, I want to use this query, but I want to check if there is a record against project_id = pId and expert_id = eId then UPDATE ml_match else INSERT project_id, expert_id, ml_match and ignore the other columns. Can you write query? Is ON DUPLICATE KEY UPDATE work only on primary key or any field name? – Khalid Usman Nov 26 '16 at 06:36
7

I know this is an old question, but the Google lead me here recently so I imagine others come here, too.

@chaos is correct: there is the INSERT ... ON DUPLICATE KEY UPDATE syntax.

However, the original question asked about MySQL specifically, and in MySQL there is the REPLACE INTO ... syntax. IMHO, this command is easier and more straightforward to use for upserts. From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Note this is not standard SQL. An example from the manual:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Edit: Just a fair warning that REPLACE INTO isn't like UPDATE. As the manual says, REPLACE deletes the row if it exists, then inserts a new one. (Note the funny "2 rows affected" in the example above.) That is, it will replace the values of all columns of an existing record (and not merely update some columns.) The behavior of MySQL's REPLACE INTO is much like that of Sqlite's INSERT OR REPLACE INTO. See this question for some workarounds if you only want to update a few columns (and not all columns) if the record already exists.

Armadillo Jim
  • 291
  • 2
  • 7
  • 1
    Old question, I know: but for anyone thinking of doing this; please keep in mind using the `REPLACE INTO` has a potential "gotcha." If you've got any "ON DELETE CASCADE" magic going on, this `REPLACE` will cause the cascade to trigger - possibly causing huge issues. See: https://stackoverflow.com/questions/4723145/what-is-the-performance-difference-between-insert-ignore-and-replace-in-mysql#comment5217271_4723200 – nchopra Aug 26 '22 at 15:17