错误信息:
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 ......一大长串查询......) 别名);
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。