MySQL Error Code: 1349. View’s SELECT contains a subquery in the FROM clause

databaseMySQLsql

I am trying to create a view but get the following error:

View's SELECT contains a subquery in the FROM clause

I am running the following command. I can't seem to figure out how to substitute the nested selects with joins. Any help would be much appreciated!

create view student_fee_basic as
select fsbc.*, ffp.name, ffp.amount 'fee'
from
(select sbc.*, ffc.name 'fname', ffc.id 'fid'
    from (select s.admission_no, s.first_name, bc.id 'bid', bc.code, bc.name
        from (select b.id, b.name, c.code
            from batches b, courses c
            where b.name = '2014-2015'
            and b.course_id = c.id) bc
        left join students s on bc.id = s.batch_id) sbc
    left join finance_fee_categories ffc on ffc.batch_id = sbc.bid
    where ffc.name = 'Basic Monthly') fsbc
left join finance_fee_particulars ffp on ffp.finance_fee_category_id = fsbc.fid;

Best Answer

MySQL does not support subqueries in views:

Subqueries cannot be used in the FROM clause of a view.

The documentation is here.

The easiest fix is to use a series of different views for each level.

You can probably rewrite this query to remove the subqueries. However, I find it very hard to help without explicit joins.

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternatively, try nesting the subquery deeper into a from clause ...

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.

... but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

MySQL select 10 random rows from 600K rows fast

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

Best Answer

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

MySQL select 10 random rows from 600K rows fast

Related Topic