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

score 6 · Accepted Answer · answered Sep 18 '10 at 18:36

6

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'

answered Sep 18 '10 at 18:36

OMG Ponies

325,700
82
523
502

+1 Nice! Nothing like a tiny workaround. Curious if MySQL would support something like: `UPDATE M SET... FROM Members M WHERE... GROUP BY ... HAVING....` (no MySQL here ATM). Thoughts? – p.campbell Sep 18 '10 at 18:38
@p.campbell: I think the JOIN option would be cleaner. Too bad the JOIN in UPDATE & DELETE isn't supported in most other databases. – OMG Ponies Sep 18 '10 at 18:41

score 0 · Answer 2 · answered Sep 18 '10 at 18:32

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.

update rows with duplicate entries

2 Answers2