Is there an easy way to INSERT an row when not exists, or to UPDATE if it exists, using one MySQL query?
views:
1350answers:
2
+15
A:
Yes, 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
2009-08-02 13:35:22
Wow, that's awesome. Do other RDMSs have this feature?
Brian Ramsay
2009-08-02 13:38:40
Yeah, I believe SQL Server's equivalent is called `MERGE`. In general, the concept is often referred to as `"UPSERT"`.
chaos
2009-08-02 13:40:34
blub
2009-08-02 13:40:52
Make a unique index on GEB and Topic
Chacha102
2009-08-02 13:41:51
If you're running tables without keys then you have bigger problems than trying to save 1 query per execution (which is all this will do, as it bypasses doing a SELECT to check if exists)
iAn
2009-08-02 13:42:30
@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
2009-08-02 13:43:41
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
2009-08-02 13:56:28
@Brian: Oracle's equivalent is also called `MERGE` but I'm not sure if its syntax is identical to SQL Server's.
Ken Keenan
2009-08-02 14:20:17
@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
2009-08-02 14:22:09