mysql - mysql如何删除多个表中同一列？

Question

我有 2 个具有以下结构的表，其中没有 (PK/FK) 的关系。

======== country =======
+----+-------+---------+
| id | name  | visible |
+----+-------+---------+
|  1 | kkk   |       0 |
|  2 | mmm   |       1 |
|  3 | ttt   |       1 |
|  4 | kkkkk |       0 |
+----+-------+---------+

============ city =============
+----+------+---------+-------+
| id | name | visible | c_id  |
+----+------+---------+-------+
|  3 | k333 |       0 |     1 |
|  2 | k222 |       1 |     1 |
|  1 | kkk  |       1 |     1 |
|  4 | k444 |       0 |     2 |
|  6 | k666 |       0 |     2 |
+----+------+---------+-------+

我使用 country.id 和 city.country_id 作为两个表之间的链接。我试图删除可见性值为 0 的国家和城市。搜索后我想出了这段代码:

delete country , city from country, city where city.country_id = country.id and country.id in (select id from country where visible = 0);

但它返回以下错误:

ERROR 1093 (HY000): You can't specify target table 'country' for update in FROM clause

我尝试像这样将 JOIN 与 WHERE 结合使用:

DELETE country , city 
FROM country JOIN city 
ON city.country_id = country.id 
WHERE  country.visible = 0

效果很好，但是还有一行值为 0 的行没有被删除。

======== Country ======
+----+-------+---------+
| id | name  | visible |
+----+-------+---------+
|  2 | mmm   |       1 |
|  3 | ttt   |       1 |
|  4 | kkkkk |       0 |
+----+-------+---------+

Answer 1

@Mihai 在我再次检查你的代码后，我只是添加了 WHERE 并且它像这样工作:

DELETE country, city 
FROM country 
LEFT JOIN city 
ON city.country_id = country.id 
WHERE country.visible = 0;