這個發生的情況會在 update / delete sql command, 這個 sub query 用在 select 時又是ok, 這個情況只有在 mysql 會遇到, 在 MSSQL 就合法,
mysql 解法, 是產生一個 sub sub query , 並把 sub usb query 多一個 as newTable 變成 sub query 的 table 就OK.
( select your_field from ( select your_field from real_table where your_field=your_where ) as newTable );
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
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 tablein 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
WHEREclause to limit the results, too.