5

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:

  1. 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)
)

  2. 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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Baylock
  • 1,246
  • 4
  • 25
  • 49

3 Answers3

7
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)
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Thank you Lajos Arpad for your answer. This seems a very elegant query indeed. – Baylock May 08 '12 at 01:53
  • I was happy to help. Also, if you are sure your user_id is unique in the users table I have an even simpler solution. – Lajos Arpad May 08 '12 at 01:54
  • I've edited my answer, to provide an alternative for the case when user_id is unique in the users table. – Lajos Arpad May 08 '12 at 01:57
  • My id's are all unique (autoincrement), whatever the table of this app. I'm all eyes and ears for a simpler solution (to all the oher participants, please consider that I will study all your answers as there are also very interresting, thank you) – Baylock May 08 '12 at 01:57
  • Perfect, thanks (didn't try the query yet but I believe it'll be ok) – Baylock May 08 '12 at 01:59
  • 1
    @Lajos, are you sure the simpler solution works? To me it looks like you are only deleting rooms where initiating and target are the same user. – bfavaretto May 08 '12 at 01:59
  • You are right, user_connected is not unique. I'll edit my answer – Lajos Arpad May 08 '12 at 02:00
  • @Babylock, the simpler solution was incorrect, my first solution should be used instead. – Lajos Arpad May 08 '12 at 02:06
  • Ok, I read the objection and will consider the previous answer. I thank all of you for this (it's Baylock actually!). – Baylock May 08 '12 at 02:07
  • @Baylock, I'm not arguing with Lajos, I'm actually learning from his answers. I even upvoted this one. – bfavaretto May 08 '12 at 02:12
  • @bfavaretto:I know,it's the way I took it. By "objection", I meant that you questionned the last query as it didn't answer the need. From my perspective, I'm learning from the 3 of you. I never thought there would be so many ways to achieve the same result and that each of these would be more or less optimized. The "Join" thing isn't new for me but it just doesn't completely make sense to me because it's not intuitive. I avoid it as much as I can but once in a while I have to face it and learn it but then I give up as it's beyond my understanding (I never find how to make it fit my scenarios). – Baylock May 08 '12 at 02:25
1

To target the users, you need to JOIN table users twice, once for the initiating user, and another one 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 delete those rooms, you can use the above 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
)
bfavaretto
  • 71,580
  • 16
  • 111
  • 150
  • The query looks to be correct, but it's overcomplicated and has a complexity of O(n^4), while this is solvable in a complexity of only O(n^2), see my answer. – Lajos Arpad May 08 '12 at 01:36
  • You cannot delete from a table and select from the same table in a subquery. – Ezequiel Muns May 08 '12 at 01:36
  • @Ezequiel Muns: Yes you can if you use table aliases. bfavaretto used the alias room for the rooms table. You can test it anytime by creating these tables, filling them with rows and run bfavaretto's query. It will work, but it will be too slow in my opinion. – Lajos Arpad May 08 '12 at 01:45
  • @LajosArpad, that's interesting, I tend to favor joins instead of subqueries for performance reasons. Being a self-learner, and I never really understood the big-o notation, but I'll definitely look into this further! Thank you for your comment! – bfavaretto May 08 '12 at 01:47
  • Thank you very much bfvaretto for your help. If you don't mind, I will go for the simplest query given by Lajos Arpad. – Baylock May 08 '12 at 01:51
  • @LajosArpad I disagree, did it, and got this: `Error Code: 1093. You can't specify target table 'rooms' for update in FROM clause` on MySQL 5.1. http://dev.mysql.com/doc/refman/5.1/en/delete.html confirms my assertion. – Ezequiel Muns May 08 '12 at 03:28
  • @EzequielMuns, you are not right, see here: http://stackoverflow.com/questions/8527569/mysql-delete-with-subquery. bfavaretto missed the AS keyword, probably because a typo. Table aliases are used exactly for this purpose, please read more about table aliases before you say that you can't delete from a table and select from the same table in a subquery. This assertion is wrong and will mislead any inexperienced person reading it. – Lajos Arpad May 08 '12 at 11:25
  • @Lajos, as far as I know the `AS` keyword is optional for table aliases. I always use it on column aliases on the `SELECT` clause, but never on table aliases on `FROM`. – bfavaretto May 08 '12 at 13:44
  • @LajosArpad Please read the MySQL reference I linked, where you will find the exact sentence: "Currently, you cannot delete from a table and select from the same table in a subquery." The example that you linked is actually deleting from one table while self joining to a subquery on the same table (effectively creating a temporary table and thus bypassing this limitation) this has little to do with aliases. This is a special case way too contrived for the average inexperienced programmer, thus as a general rule, as the official documentation says: You can't do this :) – Ezequiel Muns May 09 '12 at 01:21
  • Also, the changes required to bfavaretto's query for that trick to work are far more extensive than just using the AS keyword. Try it, you'll see what I mean. – Ezequiel Muns May 09 '12 at 01:25
  • @Ezequiel and Lajos: I learnt SQL from MS SQL Server, where that kind of query is okay. Sometimes I find out simple stuff like that won't work on MySQL, and I hate MySQL for that... So yes, my query can be wrong, I'll try it later and delete this answer if so. – bfavaretto May 09 '12 at 01:28
  • @bfavaretto Fair enough, I too prefer PgSQL. I don't think deleting the answer is the way to go, we all learn more by having this discussion :) (so long as people can keep their egos in check) – Ezequiel Muns May 09 '12 at 01:32
  • @bfavaretto and Ezequiel: Ezequiel is right, MySQL doesn't support this kind of delete, therefore this answer should be deleted or edited. MS SQL for instance supports this use case. Another reason why MySQL shouldn't be recommended. – Lajos Arpad May 09 '12 at 09:16
1

First, to select these rooms, you'll need to join to the users table twice:

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

Note: The use of two distinct aliases for the table users, so as to differentiate the two joins. Also, I' using JOIN syntax, rather than the older comma joins which are discouraged

To delete the rooms, you'll need a multi-table delete:

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
Ezequiel Muns
  • 7,492
  • 33
  • 57
  • The complexity of this query is O(n^4), the problem can be solved with a complexity of only O(n^2), so this will be slow. Also, instead of DELETE r you can only write delete, because r is specified in the from clause. I'm not sure whether the syntax of DELETE r works. Did you test your code? – Lajos Arpad May 08 '12 at 01:49
  • Thank you very much Ezequiel Muns for your help. If you don't mind, I will go for the simplest query given by Lajos Arpad. – Baylock May 08 '12 at 01:52
  • @LajosArpad Does indeed. The DELETE r tells the query to only delete rows in rooms, not touching any of the joined rows in users. FYI: http://dev.mysql.com/doc/refman/5.1/en/delete.html – Ezequiel Muns May 08 '12 at 03:30