Search code examples
mysqlsqlmysql-error-1093

Using EXISTS with MySQL

I have this simple query that works on all other database systems, but fails with MySQL:

UPDATE points p 
SET p.userid = 5224 
WHERE p.userid = 2532 
AND NOT EXISTS (
    SELECT 1
    FROM points q
    WHERE q.userid = 5224
    AND q.game = p.game
)

I get the following error message: 

#1093 - You can't specify target table 'p' for update in FROM clause

Is there any workaround?

Solution

  • You can't alias the main table in an UPDATE clause. This should work:

    UPDATE points 
SET userid = 5224 
WHERE userid = 2532 
AND NOT EXISTS (
    SELECT 1
    FROM points q
    WHERE q.userid = 5224
    AND q.game = points.game
)