Deleting WordPress records based on Nested Selects

Last week I was helping with a task for deleting terms from the WordPress database based on an array of IDs from a select statement.

Since I have a thing for database systems (spent a few years playing in various mid-size and large projects with Oracle and PostgreSQL databases before migrating to MySQL) I thought it was a 20sec job to build the query having the select ready and delete the data. However, I got stuck in the process and took me about 15min to actually form the proper statement.

The final code looks like that (with a bit different where clause for the nested select):

[sql]

DELETE FROM
wp_terms
WHERE term_id IN
( SELECT * FROM (
SELECT wp_terms.term_id
FROM wp_terms
JOIN wp_term_taxonomy
ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE taxonomy = ‘product_tag’
AND count = 0
) as T
);

[/sql]

Normally what a nested select is used for is to fetch some entries to be used in a where clause in the main query, avoiding the limitations of the ANSI SQL to not have variables. Alternatively, one could either build stored procedures (to take advantage of variables, loops (cursors) and conditionals) or create temporary tables to fetch the data – both not being optimal for a single transaction.

Where MySQL differs from the regular RDBMS is the inability to fetch and update data in a table at the same time. My initial form was in the following order: “delete from X where term_id in ( select term_id from X where … )” but applying both retrieval and removal operations on the same table is prohibited by the MySQL team.

What works here is using a buffer table and assigning an alias to the table. So, for building the query, you need to follow these steps:

  1. write your SELECT statement to properly fetch the array of IDs to be deleted later
  2. write your DELETE statement as you would have written it if you had the static array of IDs
  3. wrap you SELECT statement in a “SELECT * FROM (initial select here) as T

Using the buffer table and adding the alias to it are the crucial step for MySQL to handle both delete and select within the same table.

4 thoughts on “Deleting WordPress records based on Nested Selects”

  1. Stephen Cronin says: July 17, 2013 at 3:31 pm

    Hi Mario,

    Impressive MySQL chops. I know joins always melt my brain! But I’m curious why you wouldn’t just do this in functions.php or a custom plugin:

    $categories = get_terms( ‘category’, array( ‘fields’ => ‘ids’, ‘hide_empty’ => false ) );
    foreach( $categories as $value ) {
    wp_delete_term( $value, ‘category’ );
    }

    Personally, I’d stick to the WordPress functions, simply because I understand them more easily than complex MySQL statements, but I’m curious whether there is any advantage to doing it in MySQL. I guess it may be more efficient?

    Cheers,
    Stephen

  2. Stephen Cronin says: July 17, 2013 at 3:58 pm

    Whoops, for the taxonomy you’re working with, you’d obviously substitute product_tag for category, ie


    $terms = get_terms( 'product_tag', array( 'fields' => 'ids', 'hide_empty' => false ) );
    foreach( $terms as $value ) {
    wp_delete_term( $value, 'product_tag' );
    }

    Also, for anyone who is trying to copy the code in the previous comment – the quotes have been converted to pretty quotes which will break the code. This time I’m wrapping the code in code tags, but please check this before using the code.

    Cheers,
    Stephen

  3. Mario Peshev says: July 18, 2013 at 2:49 am

    @Stephen – thanks for the snippet, already replied on Twitter that it’s for a few Ks of gigs database so performance matters big time, especially for single-use operations. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *