从子选择中删除id为逗号的行(使用SQLite，使用MySQL失败)

Question

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

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

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

现在我想删除“孤立的”播放器:任何在播放器中出现的无效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

如果in子句包含同一表中的引用，则似乎不可能删除该表中的记录。

如何在同时兼容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创建一个临时表，并允许处理Delete命令。

Answer 2

可能有两种选择。

使用额外子查询。

查询

delete 
from 
  player 
where 
  player_id in (
    select 
     player_alias.player_id
    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 player_alias
  )

请参阅demo

或者使用连接执行“多个”表删除，我会选择这样做，因为使用正确的索引可以更好地优化查询。

查询

DELETE 
 player.*
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 player_delete
INNER JOIN 
 player 
ON
 player.player_id = player_delete.player_id

请参阅demo

两次删除都将保留

| player_id |       name | game_id |
|-----------|------------|---------|
|       111 |  PlayerOne |      G1 |
|       222 | Player Two |      G1 |

编辑

带有连接的“多个”表删除可以在源外重写(简单得多)。

在性能和内存方面，假设MySQL可以对此查询使用正确的索引，因为FROM (SELECT ..) AS <alias> (Derived Table)也会强制MySQL将其作为internal temporary table处理，那么额外的加号对性能和内存也更好

查询

  delete
    P.*
  from
    player P
  left join game G on G.game_id = P.game_id
  where G.game_id is null 

请参阅demo