mysql - 在 mysql 中使用 DISTINCT SELECT 更新表

Question

假设我有一张表，其中包含关于

的信息

game (PRIMARY INT id, TINYINT offline)

第二个表格包含有关该游戏的详细信息:

gamedetail (PRIMARY INT id, INT game_id (fk to game table), TINYINT offline) 

详细信息会经常从各种程序中更新。我在那里设置了细节的离线标志。我没有设置游戏本身离线标志的编程可能性。 (但是，如果我找到在线详细信息，我会将游戏的离线标志设置为 0)。但我想通过更新查询在数据库中设置此信息。这个想法是这样的选择:

SELECT DISTINCT game.id FROM game 
    LEFT JOIN gamedetail AS gdon 
           ON (gdon.game_id = game.id AND gdon.offline = 0)
    LEFT JOIN gamedetail AS gdoff 
           ON (gdoff.game_id = game.id AND gdoff.offline = 1)
WHERE gdoff.id IS NOT NULL AND gdon.id IS NULL;

这很好地为我提供了所有只有离线游戏详细信息的游戏。所以我想将其作为像这样的 UPDATE 语句的输入:

UPDATE game SET game.offline=1 WHERE game id IN (
    SELECT DISTINCT game.id FROM game 
        LEFT JOIN gamedetail AS gdon 
               ON (gdon.game_id = game.id AND gdon.offline = 0)
        LEFT JOIN gamedetail AS gdoff 
               ON (gdoff.game_id = game.id AND gdoff.offline = 1)
    WHERE gdoff.id IS NOT NULL AND gdon.id IS NULL;

)

不幸的是，这在 mysql 中失败了，因为 ERROR 1093 (HY000): Table 'game' is specified twice, both as a target for 'UPDATE' and as a separate source for data .

我的问题是如何将更新语句更改为可以在 mysql 中运行的语句？

编辑:更正了 WHERE 条件

Answer 1

将您的查询包装在子查询中，如下所示:

UPDATE game SET game.offline=1 WHERE game.id IN (
  SELECT * FROM (
    SELECT DISTINCT game.id FROM game 
        LEFT JOIN gamedetail AS gdon 
               ON (gdon.game_id = game.id AND gdon.offline = 0)
        LEFT JOIN gamedetail AS gdoff 
               ON (gdoff.game_id = game.id AND gdoff.offline = 1)
    WHERE gdon.id IS NOT NULL AND gdon.id IS NULL;
  ) t
)