Bug #23413 "ERROR 1046 (3D000): No database selected" occurs when executing DELETE FROM
Submitted: 18 Oct 2006 8:45 Modified: 27 Jul 2007 13:34
Reporter: Siu Ching Pong (Asuka Kenji) (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.24a-community-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: 1046, 3D000, delete, DELETE FROM, ERROR 1046, ERROR 1046 (3D000), No database selected

[18 Oct 2006 8:45] Siu Ching Pong (Asuka Kenji)
Description:
MySQL returns "ERROR 1046 (3D000): No database selected" when executing "DELETE FROM ... USING ..." with table aliases.

How to repeat:
Execute the following statements in MySQL client:

CREATE TABLE test.testA (
    colA INT PRIMARY KEY
);

CREATE TABLE test.testB (
    colB INT PRIMARY KEY,
    colA INT NOT NULL,
    CONSTRAINT fk_B_A FOREIGN KEY (colA) REFERENCES test.testA (colA)
);

DELETE FROM A,
            B
USING       test.testA A
JOIN        test.testB B ON A.colA = B.colB;

----------------------------------------------------------------

It succeeds if the last statement is changed to:

DELETE FROM test.testA,
            test.testB
USING       test.testA
JOIN        test.testB ON test.testA.colA = test.testB.colB;

----------------------------------------------------------------

It also succeeds if "USE test;" is executed first before the last statement.
[18 Oct 2006 10:05] Hartmut Holzgraefe
verified using 5.0bk
[18 Oct 2006 10:11] Hartmut Holzgraefe
mysqltest test case

Attachment: bug23413.tgz (application/x-gtar, text), 885 bytes.

[30 Nov 2006 22:36] Konstantin Osipov
http://dev.mysql.com/doc/refman/5.0/en/delete.html
 Note: If you provide an alias for a table, you must use the alias when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

This explanation also applies to the case and hand.
[15 Feb 2007 12:34] Sveta Smirnova
Bug #26370 was marked as duplicate of this one
[27 Jul 2007 13:35] Konstantin Osipov
Duplicate of Bug #27525 table not found when using multi-table-deletes with aliases over several databas
[11 Jul 2016 20:46] Daniel Fisher
This bug does not appear to be fixed perfectly. Consider the following case, in whcih the delete only affects a single table. In this case, MySQL 5.7 still produces a 1046 error.

How to repeat:
Execute the following statements in MySQL client:

CREATE TABLE test.testA (
    colA INT PRIMARY KEY
);

CREATE TABLE test.testB (
    colB INT PRIMARY KEY,
    colA INT NOT NULL,
    deleteMe INT NOT NULL,
    CONSTRAINT fk_B_A FOREIGN KEY (colA) REFERENCES test.testA (colA)
);

DELETE FROM B
USING     test.testA A
JOIN        test.testB B ON A.colA = B.colB
WHERE B.deleteMe <> 0;

Server Version Info: 5.7.12
MySQL Community Server (GPL)
Compiled for: osx10.11   (x86_64)