How do I use "IGNORE" in a database query?

Question

I would like to run a query similar to INSERT IGNORE INTO the_table (nid, language) VALUES (8, 'Chinese'), but I cannot find any documentation on how the "IGNORE" statement works with the Drupal DB API.

Does anyone know how to achieve that?

db_merge('the_table')
  ->key(array('nid' => 8, language' => Chinese))
  ->fields(array(
      'nid' => 8,
      'language' => Chinese,
  ))
  ->updateExcept('nid','language')
  ->execute();

Would the code above be the equivalent of my initial query ?

EDIT: updateExcept method doesn't exist, no idea why it s still in the documentation

Answer 1

What you want is a MERGE query with no UPDATE part. Something like this should work:

db_merge('the_table')
  ->key(array(
      'nid' => 8,
      'language' => Chinese,
  ))
  ->insertFields(array(
    'nid' => 8,
    'language' => Chinese,
  ));
  ->execute();

Answer 2

db_insert() does not support IGNORE because this is not SQL standard but a mysql-specific extension. db_merge() is not what you want I think, because it either INSERTS or UPDATEs a row. There are two possible ways to work around this:

• You can simply use db_query() and pass a query string to it. However, this means that your code/module only works with MySQL and can not be used with PostgreSQL. This might be OK for a custom module for a specific site, but I would nevertheless strongly recommend to chose option 2 below. For a contributed module, this is wrong, obviously.

• The only clean way out of this is that you first need to check if such a row already exists with a normal SELECT query and if not, execute a normal INSERT query. Note that this method might be prone to concurrency issues, if two requests happen at the same time. You could work around that with a transaction and blocking write access to that table, but this might on the other hand slow down performance...

Answer 3

Instead of using IGNORE, I think you'll be interested to db_merge(), which adds or updates your data according to the query you execute.