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_name
This field identifies the theme. The parent theme has the value
theme_mods_decode
; the child theme,theme_mods_decode-child
.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.
1 comment:
Hi,
Thanks for sharing valuable information on Linux
Post a Comment