Shulou(Shulou.com)06/03 Report--

I. demand

To update the status field of data whose base_info.name begins with wl_ to 2

II. Implementation

Update base_info

Set a.status=2

Where (select id from base_info where name 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 that a subquery cannot be the same table when modifying a table.

III. Optimization

Solve this problem through temporary tables

Method 1:

Update base_info

Set a.status=2

Where id in (select * from (select id from base_info where name like'% xx%') tmp)

Method 2:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.