Use REPLACE in update query using db_update

Question

I need to run an update query that involves using the SQL REPLACE and LEFT functions, and I need to do it with three different queries for multiple tables, so I'd like to do it in a loop. The problem I'm having is getting the REPLACE function to be interpreted properly. The query I need to run is this:

UPDATE field_data_field_myfield
SET field_myfield_value = 
REPLACE(field_myfield_value, 
'old_value', 'new_value');

From what I've found, I need to use the expression function like so:

foreach ($tables as $table => $field) {
  $num_updated_1 = db_update($table)
    ->expression($field, 'replace(:field, :old, :new)', array(
      ':field' => $field,
      ':old' => 'old_value',
      ':new' =>  'new_value',
    ))
    ->execute();
}

However, when I run it like this, the field just gets updated with the field name as a string for every field.

What do I need to change for this to work?

Answer 1

The replace function doesn't work when the field is a placeholder. This will insert the field string as new value in the database. I've solved this problem removing the placeholder. Check it out:

foreach ($tables as $table => $field) {
  $num_updated_1 = db_update($table)
    ->expression($field, 'replace(' . $field . ', :old, :new)', array(
      ':old' => 'old_value',
      ':new' =>  'new_value',
    ))
    ->execute();
}

This way the code will work perfectly. I hope this may help someone.

Answer 2

Correct way to replaced value if use Drupal 7 PDO:

db_update('field_data_field_myfield')
  ->expression('field_myfield_value', 'replace(field_myfield_value, :old, :new)', array(
    ':old' => 'some old text',
    ':new' =>  'some new text',
  ))
  ->execute();

In your example error in this string use placeholders:

':field' => $field,

Field name cannot be passed to placeholders.