sql - 使用重复条目更新行

Question

我的情况和this other question一样，但我不想选择行，我想更新这些行。

我使用了 solution Scott Saunders made :

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

那行得通，但我想更改/更新这些条目中的行值，所以我尝试了:

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

但是我得到这个错误:

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

Answer 1

使用中间子查询来解决 1093 错误:

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

否则，在派生表上使用 JOIN:

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