You can’t specify target table for update in FROM clause

by

The problem is that MySQL, for whatever inane reason, doesn’t allow you to write queries like this:

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

That is, if you’re doing an UPDATE/INSERT/DELETE on a table, you can’t reference that table in an inner query (you can however reference a field from that outer table…)

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

This apparently causes the necessary fields to be implicitly copied into a temporary table, so it’s allowed.

I found this solution here. A note from that article:

You don’t want to just SELECT * FROM table in the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a good WHERE clause to limit the results, too.

More Related Contents:

  1. MySQL: Alternatives to ORDER BY RAND()
  2. Auto Increment after delete in MySQL
  3. Why would an IN condition be slower than “=” in sql?
  4. PostgreSQL GROUP BY different from MySQL?
  5. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  6. MySQL dump by query
  7. Delete sql rows where IDs do not have a match from another table
  8. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  9. MySQL – Selecting data from multiple tables all with same structure but different data
  10. What does DELIMITER // do in a Trigger?
  11. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  12. MySQL equivalent of DECODE function in Oracle
  13. How do you version your database schema? [closed]
  14. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  15. MySQL, Get users rank
  16. Mysql: how to select groups having certain values?
  17. How to fill date gaps in MySQL?
  18. Adding a new SQL column with a default value
  19. Boolean vs tinyint(1) for boolean values in MySQL
  20. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  21. Function for week of the month in mysql
  22. Find rows that have the same value on a column in MySQL
  23. what is the difference between GROUP BY and ORDER BY in sql
  24. How to transform vertical data into horizontal data with SQL?
  25. What does collation mean?
  26. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  27. MySQL: Count records from one table and then update another
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment