Subqueries Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = "You can’t specify target table ’x’ for update in FROM clause" UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2); SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2); SELECT (SELECT column1 + 5 FROM t1) FROM t2; SELECT (SELECT column1 FROM t1) + 5 FROM t2; DELETE FROM t WHERE ... (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...); 2698 Exception: The preceding prohibition does not apply if for the modified table you are using a derived table and that derived table is materialized rather than merged into the outer query. (See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” .) Example: Instead of this query: For another example, use this query: •In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms: 13.2.11.12 Restrictions on Subqueries Instead of this query: Development is ongoing, so no optimization tip is reliable for the long term. The following list provides some interesting tricks that you might want to play with. See also Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions” . •Move clauses from outside to inside the subquery. For example, use this query: 13.2.11.11 Optimizing Subqueries For transactional storage engines, the failure of a subquery causes the entire statement to fail. For nontransactional storage engines, data modifications made before the error was encountered are preserved. You can use a common table expression or derived table to work around this. See Section 13.2.11.12, “Restrictions on Subqueries” . In MySQL 8.0.19 and later, all of the errors described in this section also apply when using  TABLE  in subqueries. This error occurs in cases such as the following, which attempts to modify a table and select from the same table in the subquery: