Update on a table with DISTINCT SELECT in mysql

Question

Suppose I have a table with information about a

game (PRIMARY INT id, TINYINT offline)

and a second table with detail info about that game:

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

the details get updated frequently and from various programs. There I set the the offline flag of the detail. I have no programmatic possibility of setting the offline flag of the game itself. (I do set the offline flag of the game to 0 however, if I find an online detail). But I want to set this info in the database via an update query. The idea is this SELECT:

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;

This gives me nicely all games where I only have offline gamedetails. So I would like to take this as input for an UPDATE statement like this:

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;

)

This unfortunately fails in mysql, because of ERROR 1093 (HY000): Table 'game' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

My question is how to change my update statement into something that works in mysql?

Edit: corrected the WHERE condition

Answer 1

Wrap your query inside a sub query, like this:

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
)