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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
