Select and Delete with a single query

mysql, optimization, select, sql-delete

Solution

delete from rooms
where room_initiating_user_id in (select user_id from users where user_connected = 0)
  and room_target_user_id in (select user_id from users where user_connected = 0)

Problem

I have 2 tables: - users (user_id, user_connected) - rooms (room_id, room_initiating_user_id, room_target_user_id) I would like to delete all the "rooms" having both the initiating user and the target_user set to "user_connected=0" I have two problems here: How to target these users? Obviously this query won't work: ``` SELECT room_id FROM rooms,users WHERE ( (room_target_user_id=user_id) AND (user_connected=0) ) AND ( (room_initiating_user_id=user_id) AND (user_connected=0) ) ``` I would like, with the same query if possible, to delete these rooms (no problem if I use a second query but it means that this query would be triggered for each result, which is a lot. Isn't it possible to delete these rooms at once?

Original source