CODEWITHSUNDEEP
mysql
Kamal
Kamal

Reputation: 33

How to delete from multiple tables with the same column in mysql?

I have 2 tables with the below structure where there is no relation of (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 |
+----+------+---------+-------+

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: 

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

But it returns the below error: 

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

I tried to use JOIN with WHERE like this :

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

It worked well, but there is one more row which is having the value of 0 was not deleted.

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

Upvotes: 1

Views: 1591

Answers (2)

Kamal
Kamal

Reputation: 33

@Mihai After I checked your code again I just added WHERE and it worked like this:

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

Upvotes: 2

Mihai
Mihai

Reputation: 26784

Use a JOIN with a WHERE, it is much clearer.

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

ON your sample data the kkk row with visible=0 wont be deleted since it has an id of 4 which doenst exist in the city table so it won`t be picked up in the join.

Upvotes: 0

Related Questions