[SOLVED] Clean multiple numbered mysql tables

Issue

I have to update several tables to clean stacked cron jobs on a wordpress multisite install.

The network has 500+ plus sites and tables in wordpress are numbered so you will have:

wp_options
wp_1_options // for blog id 1
wp_2_options
wp_3_options
// and so on...

I was wondering if there is any wildcard I could use to replace that blog id number so it will take all the tables from 1 to x number at once. I tried with wp_*_options without any possitive result. (I'm not use to work with the DBs directly).

The query I'll be executing looks like this:

UPDATE wp_options SET option_value = '' WHERE option_name = 'cron';

Thanks.

Solution

Basically you are looking for dynamic SQL: this means using SQL to generate and execute SQL.

In MySQL, the standard approach requires creating a procedure that uses a cursor to loop through the table names and generate the queries, then use prepared statements to execute them. This can be tricky.

For a one-shot action, I would go for an intermediate option : use a SQL query to generate a list of SQL statements. We can query against INFORMATION_SCHEMA.TABLES to list the tables to update.

SELECT CONCAT(
    'UPDATE ', table_name, 
    ' SET option_value = NULL',
    ' WHERE option_name = ''cron'';'
) as sql_query
FROM information_schema.tables
WHERE table_name LIKE 'wp_%_options'

Then, all that is left to do is manually copy the results of the query to your preferred MySQL client, and execute the whole thing as a script.

NB1: carefully check the results and tests the queries before doing this in your Production database !

NB2: the accepted answer of this SO question explains thoroughly the logic of both approaches, especially the stored procedure solution.

Answered By - GMB

Answer Checked By - Dawn Plyler (WPSolving Volunteer)