Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

procmail modify subject, procmail alter subject, formail get body of message, www.xxxcon, www xxxcon, board.issociate.de, registrieren fehler dll server 0x8002801c, far2 vs near2 raid10, access abfragen aus linux, "Can't send mail: sendmail process failed with error code 69"

Links

XODOX
Impressum

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

Posted on 2006-10-31 21:06:49 by Daniel Caune

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

Report this message

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

Posted on 2006-10-31 22:42:03 by mysql-bugs

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

Report this message

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

Posted on 2006-11-21 15:21:17 by Valeriy Kravchuk

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

Report this message