title: How to delete from multiple tables with the same column in mysql?
body: <p>I have 2 tables with the below structure where there is no relation of (PK / FK).</p>
  
  <pre><code>======== 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 |
  +----+------+---------+-------+
  </code></pre>
  
  <p>I am using country.id and city.country_id as the link between the 2 tables. I am trying to delete the countries and cities where visibility value is 0. After searching I came up with this piece of code: </p>
  
  <pre><code>delete country , city from country, city where city.country_id = country.id and country.id in (select id from country where visible = 0);
  </code></pre>
  
  <p>But it returns the below error: </p>
  
  <pre><code>ERROR 1093 (HY000): You can't specify target table 'country' for update in FROM clause
  </code></pre>
  
  <p>I tried to use JOIN with WHERE like this :</p>
  
  <pre><code>DELETE country , city 
  FROM country JOIN city 
  ON city.country_id = country.id 
  WHERE  country.visible = 0
  </code></pre>
  
  <p>It worked well, but there is one more row which is having the value of 0 was not deleted.</p>
  
  <pre><code>======== Country ======
  +----+-------+---------+
  | id | name  | visible |
  +----+-------+---------+
  |  2 | mmm   |       1 |
  |  3 | ttt   |       1 |
  |  4 | kkkkk |       0 |
  +----+-------+---------+
  </code></pre>