在MySQL中使用EXISTS [英] Using EXISTS with MySQL

问题描述

我有一个适用于所有其他数据库系统的简单查询，但是对于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
)

我收到以下错误消息:

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

有什么解决方法吗?

推荐答案

您不能在UPDATE子句中为主表添加别名.这应该起作用:

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
)

这篇关于在MySQL中使用EXISTS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！