mysql - 删除带有来自子选择的 id 的行(使用 SQLite，使用 MySQL 失败)

Question

我有两个表:表“GAME”和表“PLAYER”。玩家通过 game_id 引用游戏。一个游戏可以包含多个玩家。我的应用程序应该使用 MySQL 和 SQLite，并且我不在这些表上使用外键。

我的应用程序允许通过删除 GAME 表中的一行来删除游戏。因此，一些玩家可能会引用 (game_id) 到一个不再存在的游戏，这对我来说没问题。

http://sqlfiddle.com/#!9/80baf2/5

现在我想删除“孤儿”玩家:必须删除 PLAYER 中任何具有无效 GAME_ID 的事件。如果可能，我想在单个查询中完成。

我用 SQLite 找到了一个可行的解决方案:

delete 
from 
  player 
where 
  player_id in (
    select
    P.player_id
    from player P
    left join game G on G.game_id = P.game_id
    where G.game_id is null
  )

但是在 MySQL 中同样的查询返回一个错误:

#1093 - You can't specify target table 'player' for update in FROM clause

似乎不可能删除 TABLE 中的记录，其中 IN 子句包含同一 TABLE 中的引用。

如何在与 MySQL 和 SQLite 兼容的单个查询中实现这一点？

http://sqlfiddle.com/#!9/80baf2/5

Answer 1

如下更改您的delete查询:

delete 
from 
  player 
where 
  player_id in (
    select * from ( 
        select
            P.player_id
            from player P
            left join game G on G.game_id = P.game_id
            where G.game_id is null
        ) as t
  )

原因是您无法处理锁定删除的表。使用上面的技巧Mysql创建一个临时表并允许处理删除命令。