Suppose I want to update a MySQL table with data that refers to the same table. To make it easier to follow along, I will use the following actual WordPress administration scenario.
My task is to copy the attributes of a WordPress theme named decode to its child theme named decode-child.
Theme attributes are stored in the MySQL table wp_options.
The table has 2 relevant fields:
option_nameThis field identifies the theme. The parent theme has the value
theme_mods_decode; the child theme,theme_mods_decode-child.option_valueThis is the field we want to copy from the parent theme to its child.
In essence, I want to copy the option_value of the record with option_name equals theme_mods_decode to the record with option_name equals theme_mods_decode-child.
We will use a subquery to accomplish the task. A subquery is a SELECT SQL statement within another SQL statement, say UPDATE.
My first subquery attempt resulted in an error:
update wp_options set option_value =
(select option_value from wp_options where option_name = 'theme_mods_decode')
where option_value = 'theme_mods_decode-child';
ERROR 1093 (HY000): You can't specify target table 'wp_options' for update in FROM clauseMySQL does not allow the FROM clause of a subquery to refer to the same table targeted for update.
To get around the above error, replace the table reference wp_options in the original subquery with a nested subquery (select * from wp_options):
update wp_options set option_value =
(select option_value from
(select * from wp_options) as x
where option_name = 'theme_mods_decode')
where option_name = 'theme_mods_decode-child';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0 The above solution may look like a hack to some of us. There may even be other solutions, perhaps using complex joins and unions. But I like the above approach because it is simple, both conceptually and syntactically.
