Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Ollie Saunders
DevNet Master
Posts: 3179 Joined: Tue May 24, 2005 6:01 pm
Location: UK
Post
by Ollie Saunders » Wed Apr 25, 2007 10:50 am
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)
Ollie Saunders
DevNet Master
Posts: 3179 Joined: Tue May 24, 2005 6:01 pm
Location: UK
Post
by Ollie Saunders » Wed Apr 25, 2007 11:09 am
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;
JayBird
Admin
Posts: 4524 Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:
Post
by JayBird » Wed Apr 25, 2007 11:10 am
yeah, i deleted it because i realized what you were actually trying to do!
JayBird
Admin
Posts: 4524 Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:
Post
by JayBird » Wed Apr 25, 2007 11:30 am
try this
Code: Select all
DELETE FROM `p` USING `password` `p`, `job` `j` WHERE `passwordId` = `_passwordId` AND `jobId` = 1
JayBird
Admin
Posts: 4524 Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:
Post
by JayBird » Thu Apr 26, 2007 6:02 am
You sure?
i created the tables on my server and tested the above query and it works