SQLite - load a game from games, that has not been processed - AI

PHP

-- Query 1: Select the latest game that doesn't exist in gamenewslog
SELECT *
FROM games g
WHERE g.uid NOT IN (
    SELECT DISTINCT game_uid 
    FROM gamenewslog 
    WHERE game_uid IS NOT NULL
)
ORDER BY g.insdate DESC
LIMIT 1;

-- Query 2: Insert the game details into gamenewslog and mark as processed
INSERT INTO gamenewslog (
    uid,
    insdate,
    title,
    additional,
    category,
    md5,
    results,
    errors,
    game_uid,
    game_title,
    processed
)
SELECT 
    NULL,                    -- uid (assuming auto-increment or will be generated)
    datetime('now'),         -- insdate (current timestamp)
    g.title,                 -- title from games table
    g.additional,            -- additional from games table
    g.category,              -- category from games table
    g.md5,                   -- md5 from games table
    NULL,                    -- results (initially null)
    NULL,                    -- errors (initially null)
    g.uid,                   -- game_uid equals uid from games table
    g.title,                 -- game_title
    1                        -- processed = true (using 1 for boolean true)
FROM games g
WHERE g.uid NOT IN (
    SELECT DISTINCT game_uid 
    FROM gamenewslog 
    WHERE game_uid IS NOT NULL
)
ORDER BY g.insdate DESC
LIMIT 1;

-- Alternative approach using a variable (if your SQLite version supports it)
-- This ensures you're working with the same record in both operations:

WITH latest_game AS (
    SELECT *
    FROM games g
    WHERE g.uid NOT IN (
        SELECT DISTINCT game_uid 
        FROM gamenewslog 
        WHERE game_uid IS NOT NULL
    )
    ORDER BY g.insdate DESC
    LIMIT 1
)
INSERT INTO gamenewslog (
    uid,
    insdate,
    title,
    additional,
    category,
    md5,
    results,
    errors,
    game_uid,
    game_title,
    processed
)
SELECT 
    NULL,                    -- uid
    datetime('now'),         -- insdate
    lg.title,               -- title
    lg.additional,          -- additional
    lg.category,            -- category
    lg.md5,                 -- md5
    NULL,                   -- results
    NULL,                   -- errors
    lg.uid,                 -- game_uid
    lg.title,               -- game_title
    1                       -- processed = true
FROM latest_game lg;