mysql 1093_mysql ERROR 1093 (HY000)

错误信息:

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

该问题原因是在mysql中，不能先select出同一表中的某些值，再update这个表(在同一语句中)

解决办法，把结果集当做一个临时表，在自我查询一遍，然后在更新

案例：

1.获取wordpress中trem_id 大于 2 小于 16 的文章 id

select a.id from wp_posts a left join wp_term_relationships b on b.object_id=a.id left join wp_term_taxonomy c on c.term_taxonomy_id = b.term_taxonomy_id left join wp_terms d on d.term_id=c.term_id where c.term_id > 2 and c.term_id < 16;

2.将查询出来的id文章状态更新

错误写法

update wp_posts set post_status='trash' where id in (select a.id from wp_posts a left join wp_term_relationships b on b.object_id=a.id left join wp_term_taxonomy c on c.term_taxonomy_id = b.term_taxonomy_id left join wp_terms d on d.term_id=c.term_id where c.term_id >= 2 and c.term_id <= 16);

这样会报 mysql ERROR 1093 (HY000): You can't specify target table 'wp_posts' for update in FROM clause 错误

正确写法

update wp_posts set post_status='trash' where id in (select id from (select a.id from wp_posts a left join wp_term_relationships b on b.object_id=a.id left join wp_term_taxonomy c on c.term_taxonomy_id = b.term_taxonomy_id left join wp_terms d on d.term_id=c.term_id where c.term_id >= 2 and c.term_id <= 16) e);

3.原理

就是避免在同一语句中，先select出同一表中的某些值，再update这个表

基本格式：

update 表A set 字段=值 where id in (select id from (select 表A.id from wp_posts a left join ......一大长串查询......) 别名);