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

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'