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)
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.24a-community-nt
• OS: Windows (Windows XP)
• 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)