This is expected behavior for MySQL post version 4.1. Check out:
http://dev.mysql.com/doc/mysql/en/DELETE.html
particularly the 'Note:' section just before the user comments. If you
use table aliases you must use those aliases to refer to the tables you
want to delete, not the table names themselves.
On Mon, 2004-07-19 at 07:49, Asim Thakker wrote:
> >Description:
>
> Multi-delete stops working in all forms when used along with table
> aliases, it gives an error saying
>
> ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
>
> Which definately is NOT the case. After trying all kinds of
> combinations I reliased that the problem shows
>
> up only when multi-delete is used with table aliases.
>
> >How-To-Repeat:
>
> create table test_base(id INT(11) primary key);
>
> create table test_lead(lead_id INT(11) primary key);
>
>
>
> insert into test_base values(1);
>
> insert into test_base values(2);
>
> insert into test_base values(3);
>
> insert into test_base values(4);
>
> insert into test_base values(5);
>
>
>
> insert into test_lead values(1);
>
> insert into test_lead values(2);
>
> insert into test_lead values(3);
>
>
>
> --PROBLEM--
>
> delete test_base, test_lead from test_base a join test_lead b on
> (a.id = b.lead_id);
>
> ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
>
>
>
> delete test_base, test_lead from test_base a, test_lead b where
> (a.id = b.lead_id);
>
> ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
>
>
>
> delete from test_base, test_lead using test_base a, test_lead b
> where (a.id = b.lead_id);
>
> ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
>
>
>
> show tables;
>
> +----------------+
>
> | Tables_in_test |
>
> +----------------+
>
> | test_base |
>
> | test_lead |
>
> +----------------+
>
> 2 rows in set (0.00 sec)
>
>
>
> >Fix:
>
> delete test_base, test_lead from test_base, test_lead where
> (test_base.id = test_lead.lead_id);
>
> Query OK, 6 rows affected (0.01 sec)
>
>
>
> OR
>
>
>
> delete test_base, test_lead from test_base join test_lead on
> (test_base.id = test_lead.lead_id);
>
> Query OK, 0 rows affected (0.00 sec)
>
>
>
>
>
> >Submitter-Id: Asim Thakker
>
> >Originator: Asim Thakker
>
> >Organization:
>
> Data Management
>
> >MySQL support: none licence
>
> >Synopsis: BUG in Multi-Delete when table aliases included in query
>
> >Severity: serious
>
> >Priority: medium
>
> >Category: mysql
>
> >Class: sw-bug
>
> >Release: mysql-4.1.3-beta-standard (Official MySQL-standard binary)
>
> >Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.1.3-beta, for
> pc-linux on i686
>
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
>
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
>
> and you are welcome to modify and redistribute it under the GPL license
>
>
>
> Server version 4.1.3-beta-standard
>
> Protocol version 10
>
> Connection Localhost via UNIX socket
>
> UNIX socket /tmp/mysql.sock
>
> Uptime: 3 hours 11 min 41 sec
>
>
>
> Threads: 2 Questions: 24763 Slow queries: 20 Opens: 465 Flush tables: 1
> Open tables: 36 Queries per second avg: 2.153
>
> >C compiler: 2.95.3
>
> >C++ compiler: 2.95.3
>
> >Environment:
>
> <machine, os, target, libraries (multiple lines)>
>
> System: Linux damagecase 2.4.23 #7 SMP Thu Dec 4 14:05:19 EST 2003 i686 i686
> i386 GNU/Linux
>
> Architecture: i686
>
>
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
> /usr/bin/cc
>
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
>
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/usr/share/info --enable-shared --enable-thread\
>
> s=posix --disable-checking --host=i386-redhat-linux --with-system-zlib
> --enable-__cxa_atexit
>
> Thread model: posix
>
> gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
>
>
>
> Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
> CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' L\
>
> DFLAGS='' ASFLAGS=''
>
> LIBC:
>
> lrwxrwxrwx 1 root root 14 Aug 7 2003 /lib/libc.so.6 ->
> libc-2.2.93.so
>
> -rwxr-xr-x 1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so
>
> -rw-r--r-- 1 root root 2233342 Sep 5 2002 /usr/lib/libc.a
>
> -rw-r--r-- 1 root root 178 Sep 5 2002 /usr/lib/libc.so
>
> Configure command: ./configure '--prefix=/usr/local/mysql'
> '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/m\
>
> ysql/bin' '--with-comment=Official MySQL-standard binary'
> '--with-extra-charsets=complex' '--with-server-suffix=-standard' '-\
>
> -enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
> '--disable-shared' '--with-client-ldflags=-all-stati\
>
> c' '--with-mysqld-ldflags=-all-static' '--with-readline'
> '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpr\
>
> o' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
>
>
>
>
>
>
>
> If any more information needed, let me know at [EMAIL PROTECTED]
>
> Thanks,
>
>
>
> Asim Thakker
>
> [EMAIL PROTECTED]
>
> ext #4176
>
>
>
>
>
> !DSPAM:40fbe134327511661126047!
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
