update rows with duplicate entries

Question

I have the same situation as this other question, but I don't want to select the rows, I want to update these rows.

I used the solution Scott Saunders made:

select * from table where email in (
    select email from table group by email having count(*) > 1
)

That worked, but I wanted to change/update a row-value in these entries, so I tried:

UPDATE `members` SET `banned` = "1" WHERE `ip` IN (
    SELECT `ip` FROM `members` GROUP BY `ip` HAVING COUNT(*) > 1
)

but I get this error:

You can't specify target table 'members' for update in FROM clause

Answer 1

This error means you can't update the members table based on criteria of the members table. In your case, you are attempting to update the members table based on a subquery of the members table. In the process you are changing that table. Think of it like a chicken before the egg paradox.

You'll need to make a temporary reference table or save/paste the ip ranges in order to run that update statement.

Answer 2

Use an intermediate subquery to get around the 1093 error:

UPDATE `members` 
   SET `banned` = '1' 
 WHERE `ip` IN (SELECT x.ip
                  FROM (SELECT `ip` 
                          FROM `members` 
                      GROUP BY `ip` 
                        HAVING COUNT(*) > 1) x)

Otherwise, use a JOIN on a derived table:

UPDATE MEMBERS 
  JOIN (SELECT `ip` 
          FROM `members` 
      GROUP BY `ip` 
         HAVING COUNT(*) > 1) x ON x.ip = MEMBERS.ip
   SET banned = '1' 

Answer 3

The fact that there are workarounds using a single query shows this isn't a "chicken and egg paradox". It's a lousy-server problem. Tell the people who make it that your initial query should work.