How To Update A Mysql Tabular Array Amongst Information From Itself

Suppose I desire to update a MySQL tabular array amongst information that refers to the same table. To become inwards easier to follow along, I volition purpose the next actual WordPress management scenario.

My job is to re-create the attributes of a WordPress subject named decode to its kid subject named decode-child. Theme attributes are stored inwards the MySQL tabular array wp_options.

The tabular array has two relevant fields:

1. option_name

   This plain identifies the theme. The nurture subject has the value theme_mods_decode; the kid theme, theme_mods_decode-child.

2. option_value

   This is the plain nosotros desire to re-create from the nurture subject to its child.

In essence, I desire to re-create the option_value of the tape amongst option_name equals theme_mods_decode to the tape amongst option_name equals theme_mods_decode-child.

We volition purpose a subquery to hand the task. Influenza A virus subtype H5N1 subquery is a SELECT SQL disceptation inside some other SQL statement, tell UPDATE.

My start subquery endeavour resulted inwards an error:

update wp_options educate 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 tabular array 'wp_options' for update inwards FROM clause

MySQL does non let the FROM clause of a subquery to refer to the same tabular array targeted for update.

To become roughly the inwards a higher house error, supersede the tabular array reference wp_options inwards the master copy subquery amongst a nested subquery (select * from wp_options):

update wp_options educate option_value =      (select option_value from        (select * from wp_options) equally x      where option_name = 'theme_mods_decode')    where option_name = 'theme_mods_decode-child';    Query OK, one row affected (0.05 sec)    Rows matched: one  Changed: one  Warnings: 0   

The inwards a higher house solution may expect similar a hack to some of us. There may fifty-fifty live on other solutions, maybe using complex joins as well as unions. But I similar the inwards a higher house approach because it is simple, both conceptually as well as syntactically.

Share this post