Tomas,
thanks that was the solution I couldn't find out.
I had to change only ON clause to master_tbl.ID=detail_tbl.Master_ID .
Thanks also to Felix, I know I could use referential integrity but I knew
there is a way how to do it without it and I was trying to find that way.
Dusan.
----- Original Message -----
From: "Tomas Rasek" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, December 21, 2005 11:12 AM
Subject: Re: Delete query problem
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
