If you are a developer or DB administrator it's a common situation that you need to update MySQL table , for example to set user statuses depending on Id column.

Let's say that we need to unvalidate users with Id < 100. If you try to run the following query:

update Users set Valid = 0
where Id in (
  select Id from Users where Id < 100
)

You'll get the error message:

#1093 - You can't specify target table 'xxx' for update in FROM clause

Explanation

MySQL doesn't allow updating the table you are already using in an inner select as the update criteria.

Many other database engines has support for this feature, but MySQL doesn't and you need to workaround the limitation. 

Solution

So, how to update the same table in MySQL? 

The trick is to use a subquery, i.e. to update a table while selecting from it itself in a subquery.

update Users set Valid = 0
where Id in (
  select Id from (
    select Id from Users where Id < 100
  ) as t
)

This is basically the same question as above, except the inner select is wrapped inside another select.

The alias in this query is important because it tells MySQL to create a temporary table from this select query and it may be used as the source criteria for the update statement.

Consider that you shouldn't use select * from table in the subquery, but rather particular field due to perfrormance issues.