If you do: SELECT * FROM MyChannels, MyPackages;
Then you should also get nothing because what you're asking the database to do is to perform a cartesian cross of the rows in both tables. Since there's no row in one of the tables, the cross will contain no rows. Ideally one could do: DELETE FROM MyChannels, MyPackages USING MyChannels FULL OUTER JOIN MyPackages; Which would delete all rows from both tables even if either table was empty. However, MySQL does not support the FULL OUTER JOIN syntax. If you know that only one table is going to be empty then you can do a LEFT OUTER JOIN and simply place the possibly-empty table on the right. However, more broadly there's the issue of why you want to delete all rows from two tables in one query. It strikes me as odd that you'd do an unbounded JOIN just to empty two tables... Why not just do: DELETE FROM MyChannels; DELETE FROM MyPackages; If you're concerned about atomicity, simply acquire a lock on both tables at once first. -JF > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:adets@;idsk.com] > Sent: Friday, August 02, 2002 1:04 PM > To: [EMAIL PROTECTED] > Subject: Multi-table delete is broken in MySQL 4.0.2 > > > >Description: > Multi-table delete is not working if one of the tables in the > delete statement is empty. > In my case I tried: > > DELETE FROM MyChannels, MyPackages USING MyChannels, MyPackages > > According to documentation this should delete all data in both tables. > In my case table MyPackages was empty (later I tried with > empty MyChannels and full MyPackages - got the same result). > In this case delete does NOTHING! > Only if _both_ tables contain at least one row all works as expected. > Tables are very simple: MyChannels contains two int columns, > MyPackages - just one int column. > > > >How-To-Repeat: > > >Fix: > > > >Submitter-Id: <submitter ID> > >Originator: > >Organization: > > >MySQL support: none > >Synopsis: Multi-table delete is broken in MySQL 4.0.2 > >Severity: serious > >Priority: high > >Category: mysql > >Class: sw-bug > >Release: mysql-4.0.2-alpha (Official MySQL RPM) > >Server: /usr/bin/mysqladmin Ver 8.35 Distrib 4.0.2-alpha, > for pc-linux-gnu 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.0.2-alpha-Max > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /var/lib/mysql/mysql.sock > Uptime: 10 days 22 hours 20 min 26 sec > > Threads: 4 Questions: 439864 Slow queries: 1 Opens: 896 > Flush tables: 1 Open tables: 36 Queries per second avg: 0.466 > >Environment: > > System: Linux monacan 2.4.9-31 #1 Tue Feb 26 07:11:02 EST > 2002 i686 unknown > 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/2.96/specs > gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110) > Compilation info: CC='gcc' CFLAGS='-O6 > -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 > -fno-omit-frame-pointer -felide-constructors > -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' > LIBC: > lrwxrwxrwx 1 root root 13 Mar 20 16:26 > /lib/libc.so.6 -> libc-2.2.4.so > -rwxr-xr-x 2 root root 1285820 Jul 22 05:28 > /lib/libc-2.2.4.so > -rw-r--r-- 1 root root 27335606 Jul 22 04:58 > /usr/lib/libc.a > -rw-r--r-- 1 root root 178 Jul 22 04:58 > /usr/lib/libc.so > lrwxrwxrwx 1 root root 10 Mar 5 13:10 > /usr/lib/libc-client.a -> c-client.a > Configure command: ./configure --disable-shared > --with-mysqld-ldflags=-all-static > --with-client-ldflags=-all-static > --with-other-libc=/usr/local/mysql-glibc > --without-berkeley-db --without-innodb --enable-assembler > --enable-local-infile --with-mysqld-user=mysql > --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ > --with-extra-charsets=complex --exec-prefix=/usr > --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share > --localstatedir=/var/lib/mysql --infodir=/usr/share/info > --includedir=/usr/include --mandir=/usr/share/man > --with-embedded-server --enable-thread-safe-client > '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 > -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 > -fno-omit-frame-pointer -felide-constructors > -fno-exceptions -fno-rtti -mpentium' CXX=gcc > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
