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

Hi,

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 =3D b.foo
AND b.foo =3D a.foo) AS d
WHERE b.foo =3D d.foo);

ERROR 1054 (42S22): Unknown column 'a.foo' in 'where clause'


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

Regards,


--
Daniel CAUNE
Ubisoft Online Technology
(514) 490 2040 ext. 3613


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

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

Greetings,

Daniel Caune wrote:
> Hi,
>
> 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 not perfect at this, but adding a to the table list for the subselect
seems to work for me, as follows:

DELETE FROM a
WHERE NOT EXISTS (
SELECT 1
FROM b, (
SELECT b.foo
FROM b, c, a
WHERE c.foo = b.foo
AND b.foo = a.foo) AS d
WHERE b.foo = d.foo);


Regards,

--
Jason K Larson


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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/bugs?unsub=gcdmb-bugs@m.gmane.org