What about
DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON
master_tbl.ID=detail_tbl.ID WHERE .....
T.R.
Dušan Pavlica napsal(a):
Hello,
I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details.
MySQL versions 4.1.10 and higher.
Could someone help me, please, to create such a query?
Example:
CREATE TABLE `master_tbl` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Desc` varchar(45) NOT NULL default '',
`Data` varchar(45) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB;
CREATE TABLE `detail_tbl` (
`Master_ID` int(10) unsigned NOT NULL default '0',
`ID` int(10) unsigned NOT NULL default '0',
`Desc` varchar(45) NOT NULL default '',
PRIMARY KEY (`Master_ID`)
) ENGINE=InnoDB;
This query works only for master records which have at least one detail record:
DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl
WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10
Thanks in advance
Dusan Pavlica
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
