Is there "UPDATE value IF NO SUCH ROW INSERT yyy" in MySQL?

Question

I want to update a table value on Mysql 5 but if the key does not exist create it.

The way I found to do it is by:

INSERT yyy ON DUPLICATE KEY UPDATE field;

The question is : is the format above less efficient than other ways to do it (As the insert will happen only once and update will happen very often)?

for example:

$result = UPDATE field; 
if (num_rows_effected($result)==0) INSERT yyy

Furthermore: Is there a better way to do this in Mysql: for example a kind of:

UPDATE value IF NO SUCH ROW INSERT yyy;

Update: For those who suggested REPLACE, here is an extension to my question: "Thanks! I need to increase a counter that is already in the table (if it exists). If not create a table row with value 1 for this column. How can I do update with this format (REPLACE)? "

Answer 1

Yes, you can use the 'replace' syntax:

REPLACE INTO table1 (key, col1, col2) values (1, 'val1','val2');

This is a feature specific to MySQL and is not necessarily implemented in other databases.

As for efficiency, my guess is that a straight update will be faster, since MySQL essentially catches the duplicate key error and handles it accordingly. However, unless you are doing large amounts of insert/updates, the performance impact will be fairly small.

Answer 2

Look at the REPLACE command, it meets your requirements.

Answer 3

There is a REPLACE also.

INSERT ON DUPLICATE KEY UPDATE will fire UPDATE triggers when it will stumble upon a duplicate key and won't violate FK's in case on UPDATE.

REPLACE will fire DELETE and INSERT triggers, and will violate FK's referencing the row being REPLACE'd.

If you don't have any triggers or FK's, then use INSERT ON DUPLICATE KEY UPDATE, it's most efficient.

You seem to be looking for this query:

INSERT
INTO table (key, counter)
VALUES (@key, 1)
ON DUPLICATE KEY UPDATE
  counter = counter + 1

You cannot do this with REPLACE unless you have selected previous value of the counter before running the query.

P. S. REPLACE appeared in MySQL before ON DUPLICATE KEY UPDATE and is being kept only for compatibility. There is no performance increase from using it.