Help with DELETE and a subquery

[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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw