Mysql Wildcard (ignore One/two Characters In Search)

I'm trying to update tables in my wordpress mu database. I want to update all wp_options tables. These tables are named like this: wp_1_options wp_2_options ...and so on. How do

Solution 1:

You can't wildcard UPDATE statements - you have to write an UPDATE statement for each table.

Untested:

CREATEPROCEDURE cleanup()
BEGINDECLARE i INTDEFAULT1;

  PREPARE stmt FROM "UPDATE ? 
                        SET option_value = REPLACE(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') 
                      WHERE option_name IN ('home', 'siteurl')"

  WHILE i <=10EXECUTE stmt USING CONCAT('wp_', i, '_options');

    SET i = i +1;
  END WHILE;

  DEALLOCATEPREPARE stmt;

END;