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:
- write your SELECT statement to properly fetch the array of IDs to be deleted later
- write your DELETE statement as you would have written it if you had the static array of IDs
- 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.
