Re: Reference to a column of a table in an inner sub-SELECT of a DELETE statement

Hi Daniel,

>> I encounter an error when I try to use a column of a table in an inner
>> SELECT of a DELETE statement.  For instance:
>>
>> CREATE TABLE a(foo int);
>> CREATE TABLE b(foo int);
>> CREATE TABLE c(foo int);
>>
>> DELETE FROM a
>>  WHERE NOT EXISTS (
>>    SELECT 1
>>      FROM b, (
>>        SELECT b.foo
>>          FROM b, c
>>          WHERE c.foo = b.foo
>>            AND b.foo = a.foo) AS d
>>    WHERE b.foo = d.foo);
>>
>> ERROR 1054 (42S22): Unknown column 'a.foo' in 'where clause'

 I think, it is expected behaviour. Oracle, for example, do the same:

SQL> CREATE TABLE a(foo int);

 Table created.

 SQL> CREATE TABLE b(foo int);

 Table created.

 SQL> CREATE TABLE c(foo int);

 Table created.

 SQL> DELETE FROM a
  2    WHERE NOT EXISTS (
  3      SELECT 1
  4        FROM b, (
  5          SELECT b.foo
  6            FROM b, c
  7            WHERE c.foo = b.foo
  8              AND b.foo = a.foo) AS d
  9      WHERE b.foo = d.foo);
            AND b.foo = a.foo) AS d
                               *
 ERROR at line 8:
 ORA-00907: missing right parenthesis


 SQL> edit
 Wrote file afiedt.buf

  1  DELETE FROM a
  2    WHERE NOT EXISTS (
  3      SELECT 1
  4        FROM b, (
  5          SELECT b.foo
  6            FROM b, c
  7            WHERE c.foo = b.foo
  8              AND b.foo = a.foo) d
  9*     WHERE b.foo = d.foo)
 SQL> /
            AND b.foo = a.foo) d
                        *
 ERROR at line 8:
 ORA-00904: "A"."FOO": invalid identifier

>> I don't figure out what is wrong there.  This SQL code works at least on
>> SQL Server or PostgreSQL.

 And it does not work on Oracle and MySQL... In case of MySQL it is
 documented in section 13.2.8.8 of our manual (for v. 5.0), "Subqueries in
 the FROM clause":

 "Subqueries in the FROM clause cannot be correlated subqueries."

 Your one refers to outer table, a, and hence it is correlated. So, it is 
not
 a bug but a documented limitation of MySQL server.

 Best regards,
-- 
 Valeriy Kravchuk, Support Engineer
MySQL AB, Kiev, Ukraine, www.mysql.com

 Are you MySQL certified?  www.mysql.com/certification 


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]