Mysql:error 1093 Update subquery

first, the demand

To update the Status field for data that begins with Base_info.name to Wl_ to 2

Second, the implementation

Update Base_info

Set a.status=2

Where (select id from base_info where name is like '%xx% ')

when executing, prompt 1093 error,

ERROR 1093 (HY000): You can ' t specify target table ' t ' for update in FROM clause.

MySQL does not support modifying a table when subqueries cannot be the same table.

Third, optimize

Resolve this issue through a temporary table

Method One:

Update Base_info

Set a.status=2

where ID in (select-ID from base_info where name like '%xx% ') tmp)

Method Two:

Update base_info b

Join (SELECT ID from base_info where act_name like ' wlq_% ') tmp

On tmp.id= b.id

Set b.expand1=2

Mysql:error 1093 Update subquery