标记重复的id会导致错误

Question

我正在尝试标记重复的事件I。

update event set repeated = 1 where eid in 
(SELECT eid FROM event group by eid HAVING count(eid) > 1);

但是，我得到了以下响应：

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

Answer 1

当使用另一个子查询来掩盖您不能从正在更新的表中选择时，您可以‘愚弄’MySQL

update event 
set repeated = 1 
where eid in 
(
    select * from 
    (
        SELECT eid FROM event group by eid HAVING count(eid) > 1
    ) x
)

Answer 2

这是MySQL的缺点之一。您可以通过将子查询包装在另一个子查询中来修复它：

update event
    set repeated = 1
    where eid in (select eid
                  from  (SELECT eid
                         FROM event
                         group by eid
                         HAVING count(eid) > 1
                        ) t
                 );