Try this ... Delete from clients where 0 = (select count(*) from branches where branches.cid = clients.cid)
Cheers FattShin -----Original Message----- From: Vikram Vaswani [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 9:51 PM To: Nils Valentin; [EMAIL PROTECTED] Subject: Re: Help with DELETE and a subquery Hey, Thanks for the help. I dont think this is possible, because MySQL will not let you delete from the same table you are reading. Is there an alternative way to do this using a subquery, you think? >ignore both previous posts. Both don't work as wanted. I just realized >that >and I will come back to you after I created the tables and made it sure. > >Sorry for the confusion. > >Best regards > >Nils Valentin >Tokyo/Japan > > >2003å¹´ 7月 25æ—¥ 金曜日 14:42ã€Nils Valentin >ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: >> Hi Vikram, >> >> just read the post once more. I made a mistake. You want to delete >> the clients with no branches you said, so the command should look >> like >> >> mysql> delete from clients where cid = (select clients.cid from >> clients left join branches using (cid) WHERE ISNULL(clients.cid); >> >> Note that cid itself is ambigous, because in both tables. >> >> Best regards >> >> Nils Valentin >> >> 2003å¹´ 7月 25æ—¥ 金曜日 14:31ã€Nils Valentin ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: >> > Hi Vikram, >> > >> > NULL is a special data type and requires special procedures. >> > >> > Try this: >> > > mysql> delete from clients where cid = (select clients.cid from >> > > mysql> clients >> > > left join branches using (cid) WHERE ISNULL(bid); >> > >> > Please make NO SPACE betwen ISNULL and (bid) as otherwise wit will >> > give you an syntax error. >> > >> > Hope that ends the problems you had. >> > >> > Best regards >> > >> > Nils Valentin >> > Tokyo/Japan >> > >> > 2003å¹´ 7月 25æ—¥ 金曜日 12:58ã€Vikram Vaswani ã•ã‚“ã¯æ›¸ãã¾ã—ãŸ: >> > > Hi all, >> > > >> > > I have the following two tables: >> > > >> > > mysql> SELECT * FROM clients; >> > > +-----+-----------------------------+ >> > > >> > > | cid | cname | >> > > >> > > +-----+-----------------------------+ >> > > >> > > | 101 | JV Real Estate | >> > > | 102 | ABC Talent Agency | >> > > | 103 | DMW Trading | >> > > | 104 | Rabbit Foods Inc | >> > > | 110 | Sharp Eyes Detective Agency | >> > > >> > > +-----+-----------------------------+ >> > > 5 rows in set (0.00 sec) >> > > >> > > mysql> SELECT * FROM branches; >> > > +------+-----+--------------------------------+------+ >> > > >> > > | bid | cid | bdesc | bloc | >> > > >> > > +------+-----+--------------------------------+------+ >> > > >> > > | 1011 | 101 | Corporate HQ | CA | >> > > | 1012 | 101 | Accounting Department | NY | >> > > | 1013 | 101 | Customer Grievances Department | KA | >> > > | 1041 | 104 | Branch Office (East) | MA | >> > > | 1042 | 104 | Branch Office (West) | CA | >> > > | 1101 | 110 | Head Office | CA | >> > > | 1031 | 103 | N Region HO | ME | >> > > | 1032 | 103 | NE Region HO | CT | >> > > | 1033 | 103 | NW Region HO | NY | >> > > >> > > +------+-----+--------------------------------+------+ >> > > 9 rows in set (0.01 sec) >> > > >> > > I need to delete all clients with no branches. I need to use a >> > > subquery to do this. Given these constraints, I came up with the >> > > following: >> > > >> > > mysql> delete from clients where cid = (select clients.cid from >> > > mysql> clients >> > > left join branches using (cid) WHERE bid is null); >> > > >> > > MySQL says: >> > > >> > > ERROR 1093: You can't specify target table 'clients' for update >> > > in FROM clause >> > > >> > > Does any one know why I am getting this error (MySQL 4.1)? Can >> > > you help me rewrite this operation *using a subquery only*? >> > > >> > > TIA, >> > > >> > > Vikram >> > >> > -- >> > --- >> > Valentin Nils >> > Internet Technology >> > >> > E-Mail: [EMAIL PROTECTED] >> > URL: http://www.knowd.co.jp >> > Personal URL: http://www.knowd.co.jp/staff/nils >> >> -- >> --- >> Valentin Nils >> Internet Technology >> >> E-Mail: [EMAIL PROTECTED] >> URL: http://www.knowd.co.jp >> Personal URL: http://www.knowd.co.jp/staff/nils > >-- >--- >Valentin Nils >Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils > -- 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]
