3

I have simple table like this:

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

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?

0

2 Answers 2

8

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

0
6

Yes, that's pretty simple.

This is what you are looking for:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

Not the answer you're looking for? Browse other questions tagged or ask your own question.