Geek asks and answers

Select and delete with a single query

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 target_user set to "user_connected = 0"

I have two problems here:

  • How do I target these users? Obviously this request will not 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 to remove these rooms with the same query (no problem if I use the second query, but that means that this query will be called for each result, which is a lot. Isn 'Can these rooms be deleted right away?

+3
source share
3 answers
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)
+4
source

-, , :

SELECT r.room_id
FROM rooms r 
    JOIN users tgt ON r.room_target_user_id = tgt.user_id
    JOIN users ini ON r.room_initiating_user_id = ini.user_id
WHERE tgt.user_connected = 0 AND ini.user_connected = 0

: , . , JOIN -,

, :

DELETE r
FROM rooms r 
    JOIN users tgt ON r.room_target_user_id = tgt.user_id
    JOIN users ini ON r.room_initiating_user_id = ini.user_id
WHERE tgt.user_connected = 0 AND ini.user_connected = 0
+1

To target users, you need to JOINtable userstwice, once for the initiating user, and another for the target user:

SELECT room.room_id
FROM rooms room
INNER JOIN users initiating
ON room.room_initiating_user_id = initiating.user_id
INNER JOIN users target
ON room.room_target_user_id = target.user_id
WHERE initiating.user_connected=0 AND target.user_connected=0

To remove these rooms, you can use this as a subquery:

DELETE FROM rooms
WHERE room_id IN (
    SELECT room.room_id
    FROM rooms room
    INNER JOIN users initiating
    ON room.room_initiating_user_id = initiating.user_id
    INNER JOIN users target
    ON room.room_target_user_id = target.user_id
    WHERE initiating.user_connected=0 AND target.user_connected=0
)
0
source

More articles:


All Articles