Submitted: 19 Jul 2004 17:11 Modified: 19 Jul 2004 20:44
Reporter: Egor Egorov Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.3 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[19 Jul 2004 17:11] Egor Egorov
        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);


        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)

Suggested 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)


        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)
[19 Jul 2004 20:44] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

[19 Aug 2008 20:15] Jenny Nunemacher
I don't understand why this isn't a bug.  I also get the same error when I use alias in a multi-table delete.  The documentation on a delete statement indicates should be able to use aliases in the table_reference clause as well as in the join_table clause.
[19 Aug 2008 20:31] Jenny Nunemacher
Nevermind my last comment.  I was trying to use the actual table name in the DELETE FROM [tbl_name] instead of using the alias specified in the table references.
[20 Jun 2011 14:51] Trent Lloyd
The trick is 

 DELETE tbl_name FROM table_reference_1 alias1, table_reference 2 alias2;

tbl_name must be the alias1 not table_reference_1.
[10 Feb 2013 3:46] Milen Georgiev
Documentation is badly written IMHO - only after reading this ticket I figured out what was wrong with my query. Maybe someone can change it or just add a good and useful example.