How to update a MySQL table with data from itself

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:

1. option_name

   This field identifies the theme. The parent theme has the value theme_mods_decode; the child theme, theme_mods_decode-child.

2. option_value

   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 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 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.