Hi Vikram, 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 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 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]
