Re: Reference to a column of a table in an inner sub-SELECT of a DELETE 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/[email protected]