mysql ERROR 1093 (HY000)

error message¶

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

The reason for the problem is that in mysql, you cannot select some values in the same table first, and then update the table (in the same statement)

The solution is to treat the result set as a temporary table, query it again, and then update

Use Case¶

1. Get the article id of wordpress¶

Get the article id of wordpresswhose trem_id is greater than 2 and less than 16

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. Update the status of the queried id article¶

• Wrong way

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);

This will report mysql ERROR 1093 (HY000): You can't specify target table'wp_posts' for update in FROM clause error.

• Correct way

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. Principle¶

Is to avoid in the same statement: first select some values in the same table, and then update the table.