MySQL Bugs: #4630: MULTI-DELETE BUG when used WITH TABLE ALIASES

Bug #4630	MULTI-DELETE BUG when used WITH TABLE ALIASES
Submitted:	19 Jul 2004 17:11	Modified:	19 Jul 2004 20:44
Reporter:	Egor Egorov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

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)

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)

               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)

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:

http://dev.mysql.com/doc/mysql/en/DELETE.html

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.

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.

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.

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.