Search code examples
mysqlsqldatabasemybb

MySQL - Delete a selection

I am trying to delete all the accounts associated with IPs that are used more than two times in my MyBB database. The following code works to "select" these users.

SELECT * FROM `mybb_forumusers` WHERE `regip` IN (
    SELECT `regip` FROM `mybb_forumusers`
    GROUP BY `regip`
    HAVING COUNT( `regip` ) > 2
)

However, I cannot successfully delete all of these users without getting errors. I have tried the following (and variations):

DELETE FROM `mybb_forumusers` WHERE `uid` IN (
    SELECT `uid` FROM `mybb_forumusers` WHERE `regip`IN (
        SELECT `regip` FROM `mybb_forumusers`
        GROUP BY `regip`
        HAVING COUNT( `regip` ) > 2
    )
)
Solution

  • I would suggest doing this using join:

    DELETE fu
    FROM `mybb_forumusers` fu JOIN 
         (SELECT `regip`
          FROM `mybb_forumusers`
          GROUP BY `regip`
          HAVING COUNT( `regip` ) > 2
         ) fu2
         ON fu2.regip = fu.regip;