Table doesn't exist in multi delete

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Table doesn't exist in multi delete

Post by Ollie Saunders »

Can anybody tell me way MySQL thinks a table doesn't exist for the purposes of a multi-delete only and is fine for everything else?

Code: Select all

mysql> SHOW CREATE TABLE `Job`\G
*************************** 1. row ***************************
       Table: Job
Create Table: CREATE TABLE `Job` (
  `jobId` int(10) unsigned NOT NULL COMMENT 'Job number',
  `title` varchar(255) NOT NULL,
  `subtitle` varchar(255) default NULL,
  `hasApproval` tinyint(1) NOT NULL default '0',
  `approvalBody` mediumtext,
  `hasPassword` tinyint(1) NOT NULL default '0',
  `_passwordId` int(10) unsigned default NULL,
  PRIMARY KEY  (`jobId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `Password`\G
*************************** 1. row ***************************
       Table: Password
Create Table: CREATE TABLE `Password` (
  `passwordId` int(10) unsigned NOT NULL auto_increment,
  `text` char(32) NOT NULL,
  `isHashed` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`passwordId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> DELETE `Password` FROM `Job` WHERE `passwordId` = `_passwordId` AND `jobId` = 1;
ERROR 1109 (42S02): Unknown table 'Password' in MULTI DELETE  -------- what's going on here?!

mysql> DELETE FROM `Password`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VERSION();
+---------------------------------+
| VERSION()                       |
+---------------------------------+
| 5.0.22-Debian_0ubuntu6.06.2-log |
+---------------------------------+
1 row in set (0.00 sec)
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Dammit you deleted your post!

Nope because that will delete from both tables won't it?

Basically there's a one to one relationship between Job and Password. Job is being modified so that it no longer has a password and so I need to delete the Password record that relates to that Job. I have jobId but nothing else. In two queries it looks like this:

Code: Select all

$jobId = 1;
$passwordId = SELECT `_passwordId` FROM `Job` WHERE `jobId` = $jobId;
DELETE FROM `Password` WHERE `passwordId` = $passwordId;
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

yeah, i deleted it because i realized what you were actually trying to do!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

try this

Code: Select all

DELETE FROM `p` USING `password` `p`, `job` `j` WHERE `passwordId` = `_passwordId` AND `jobId` = 1
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

I'll give that a try tomorrow, thanks.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Nope, same error.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

You sure?

i created the tables on my server and tested the above query and it works
Post Reply