I want to do INSERT without record in DB

Question

Attempted translation of the above question from non-native English to English:

This is a question about the fastest method to use when inserting a value into a database when the value may already be in the database. INSERT will fail when the value is there and UPDATE will fail when it is not. Which of these choices is the most desirable?

• Do SELECT first and choose INSERT or UPDATE.
• INSERT first and use UPDATE if it had a duplicate error.

If there are other good choices besides the above, please teach me.

The environment uses MySQL 4.1.

Answer 1

Update: before I get a bunch more downvotes... it should be noted this was the first reply, before the question was edited and understood.)

In all honesty it sounds like you want to know what to do if the record already exists? or is new?

Therefore what you want to do (in pseudo code)

$count = SELECT COUNT FROM TABLE WHERE CONDITION;

if($count == 1){
  UPDATE...
} else {
  INSERT...
}

Answer 2

If you want to do this in a single statement (sounds like what you want), I would recommend using the INSERT ... ON DUPLICATE KEY UPDATE syntax, as follows:

INSERT INTO table (id, someothervalue) VALUES (1, 'hi mom')
  ON DUPLICATE KEY UPDATE someothervalue = 'hi mom';

The initial INSERT statement will execute if there is no existing record with the specified key value (either primary key or unique). If a record already exists, the following UPDATE statement (someothervalue = 3) is executed.

This is supported in all versions of MySQL. For more info, see the MySQL Reference Manual page for INSERT ... ON DUPLICATE KEY UPDATE

Answer 3

Or you can use IF EXISTS syntax to check if there are rows.

Answer 4

I would build a test database and a script to benchmark the choices.

Just guessing at it, I believe doing the INSERT first and retrying with UPDATE is the faster way on average. This is because if the data is not already in the database then you saved another query. If your data is more often in the database then do an UPDATE first and retry with INSERT if it fails.

However, an error may cause a transaction to abort and need rollback. If that is bad for you then you will need to do a SELECT first.

I do not know about MySQL but I know that where I work we use a stored procedure on our PostgreSQL databases to do this. The stored procedure does a SELECT and then an INSERT or UPDATE.

Answer 5

In PHP, I would do a UPDATE first, then use mysql_affected_rows() to check if the row is updated. If not, then I would do an INSERT. Take note that the row must be changed for mysql_affected_rows() to return a result greater than 1.