Help running a MySQL query to update all wp_#_options tables in a Multisite install

Got a partial solution, but requires VPS to run it as shared hosting has a restriction on what you can do with a cursor.

Anyway,

DROP PROCEDURE IF EXISTS `update_all_options`;
DELIMITER //
CREATE PROCEDURE update_all_options(
IN db varchar(255),
IN theoption varchar(255),
IN set_val VARCHAR(255)
)
BEGIN
DECLARE table_val VARCHAR(255);

-- Declare variables used just for cursor and loop control
DECLARE no_more_rows BOOLEAN;

-- Declare the cursor
DECLARE options_cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = db AND TABLE_NAME LIKE 'wp_%options';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

OPEN options_cur;

the_loop: LOOP

FETCH options_cur
INTO table_val;

IF no_more_rows THEN
CLOSE options_cur;
LEAVE the_loop;
END IF;

SET @qry = CONCAT('UPDATE ', table_val, ' SET option_value = "', set_val, '" WHERE option_name = "', theoption, '"');
PREPARE sqlstatement FROM @qry;
EXECUTE sqlstatement;
DEALLOCATE PREPARE sqlstatement;

END LOOP the_loop;

END //
DELIMITER ;

You run it in your sql query window to create the procedure. It’ll then stick around until you need it.
To use it do:

CALL update_all_options('wpglobal', 'blog_public', '1');

To change all options with the name blog_public in the wpglobal database to 1. PHP would probably be much much easier, eh? Or so say my colleagues who came up with the above 🙂

Related Posts: