CODEWITHSUNDEEP
mysql
M.M
M.M

Reputation: 162

how to delete from mysql multiple rows depending on other rows in another table

I have a table Called phones contains all members phone numbers each member has unique id and there is another table called posts the posts table has many rows each post has its member id and another cells

I need to collect all members IDs from the posts table depending on the branches cell and delete from phones table where the members ids collected from the posts table

I tried this but it give me error message that mysql server lost the connection

delete from `phones` where `mid` in(select `uid` from `posts` where `branches` = 'contact');

Note: I'm using mysql workbench with my local server

UPDATED

this query also worked after I setup the Preferences below

Go to Edit -> Preferences -> SQL Editor and increase the parameter: DBMS connection read time out (in seconds). For instance: 86400.

Close and reopen MySQL Workbench. Kill your previously query that probably is running and run the query again.

Thanks to @scaisEdge

Upvotes: 1

Views: 40

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

your query seems correct .. anyway you can try with a inner join 

delete  `phones`.*
from `phones` 
inner join `posts`  on `phones`.`mid`  = `posts`.`uid` 
        and `posts`.`branches` = 'contact'

the inner join don't use a IN clause so you can bypass the involved limitation

if the error persist try increment the read time out

Go to Edit -> Preferences -> SQL Editor and icrease the parameter: DBMS connection read time out (in seconds). For instance: 86400.

Close and reopen MySQL Workbench. Kill your previously query that probably is running and run the query again.

Upvotes: 1

Related Questions