0

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?

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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