Updating a table with DISTINCT SELECT in mysql

Question

Suppose I have a table with information about

game (PRIMARY INT id, TINYINT offline)

and a second table with detailed information about this game:

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

Details are updated frequently and from different programs. There I set the flag to offline details. I have no software ability to set the flag offline of the game itself. (I set the offline game flag to 0, however, if I find detailed information on the Internet). But I want to install this information in the database using an update request. The idea is: 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 all the games in which I have only standalone gamedetails. Therefore, I would like to accept this as input for the UPDATE statement as follows:

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, does not execute in mysql, due to 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 
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)
SET game.offline=1
    WHERE gdoff.id IS NOT NULL AND gdon .id IS NULL;

, , , where, , WHERE gdoff.id IS NOT NULL AND gdon .id IS NULL

Answer 2

, :

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
)