exists 在mysql,在MySQL中使用EXISTS

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?

解决方案

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

)