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
Theme attributes are stored in the MySQL table
The table has 2 relevant fields:
This field identifies the theme. The parent theme has the value
theme_mods_decode; the child theme,
This 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
theme_mods_decode to the record with
We will use a subquery to accomplish the task. A subquery is a
SELECT SQL statement within another SQL statement, say
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 clause
MySQL 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.