如果存在更新，则在一个查询中插入 [英] If exists update else insert in one query

问题描述

我有一个像这样的简单表:

I have simple table like this:

+----------+---------------+-------------+
| ID (int) | KEY (varchar) | VALUE (int) |
+----------+---------------+-------------+
|        1 | asdf          |         100 |
|        2 | fdsa          |         321 |
|        3 | ecda          |         211 |
+----------+---------------+-------------+

并且我想更新KEY = 'something'的行，但是如果没有KEY = 'something'的行，我想INSERT的新行:

and I want to update row where KEY = 'something' but if there is no row where KEY = 'something' I want to INSERT new row:

+----------+---------------+-------------+
| ID (int) | KEY (varchar) | VALUE (int) |
+----------+---------------+-------------+
|        1 | asdf          |         100 |
|        2 | fdsa          |         321 |
|        3 | ecda          |         211 |
|        4 | something     |         200 |
+----------+---------------+-------------+

只有一个查询有可能吗?

Is it possible in only one query?

推荐答案

您可以使用ON DUPLICATE KEY UPDATE

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

key列上应具有UNIQUE索引

key column should have UNIQUE index on it

SQLFiddle

SQLFiddle

这篇关于如果存在更新，则在一个查询中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！