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

Question

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 |
+----+-------+---------+

Kamal · Accepted Answer · 2017-06-11 02:53:28Z

2

@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;

answered Jun 11, 2017 at 2:53

Kamal

338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai · Accepted Answer · 2017-06-11 02:45:10Z

0

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.

edited Jun 11, 2017 at 2:45

answered Jun 11, 2017 at 1:59

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

4 Comments

Kamal Over a year ago

It worked, but still for country there is one more value which is having value 0, please see the result below : +----+-------+---------+ | id | name | visible | +----+-------+---------+ | 2 | mmm | 1 | | 3 | ttt | 1 | | 4 | kkkkk | 0 | +----+-------+---------+

Mihai Over a year ago

@Kamal Not clear,but I`m guessing you need a LEFT join.

Kamal Over a year ago

With the LEFT JOIN all data from county table was deleted.

Mihai Over a year ago

@Kamal The only reason I can think of the first query didnt work is that the country didnt have a join id to the city table.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related