发布于 4年前

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 ......一大长串查询......) 别名);
©2020 edoou.com   京ICP备16001874号-3