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

Question

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

Query is correct, not sure why connection is being lost. Are these huge tables? if so probably the select query is taking too long due to lack of an index on branches? — Allen King
– Allen King, Commented Jul 9, 2017 at 18:36
@mohamad, you are beating around the bushes. If the table is huge and no index on branches, the entire table will be scanned and depending on the hardware and resources available to MySQL, it may take much longer than DB connection timeout. Have the indexes, your query is correct. — Allen King
– Allen King, Commented Jul 9, 2017 at 19:22

ScaisEdge · Accepted Answer · 2017-07-09 19:00:13Z

1

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.

edited Jul 9, 2017 at 19:00

answered Jul 9, 2017 at 18:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

10 Comments

Allen King Over a year ago

Not sure why this would work and not the one posted in the question?

ScaisEdge Over a year ago

@AllenKing if the number of elem in IN clause is high you can reach the limit .. so inner join avoid this condition

Allen King Over a year ago

ok. My suspicion is that proper indexes are not set. So even a join is not guaranteed to work. Poor indexes can only be fixed by putting proper indexes on tables :-)

ScaisEdge Over a year ago

could be .. but poor index impact on performance .. max_packect_size . impact on the volume onof data involved ..

M.M Over a year ago

@scaisEdge Error Code: 2013. Lost connection to MySQL server during query

|

Collectives™ on Stack Overflow

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

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related