mysql - 更新 MySQL Blob 字段

Question

我正在尝试从存储在同一表的另一行中的值更新几个 BLOB 字段:

UPDATE users SET user_permissions = (
                                     SELECT user_permissions 
                                       FROM users 
                                       WHERE user_id=1
                                    ) 
WHERE user_id IN (3,4,5)

但是由于以下错误而失败:

[Err] 1093 - You can't specify target table 'users' for update in FROM clause

有没有办法做到这一点？

Answer 1

您可以像这样通过连接更新来实现这一点:

UPDATE users u1
  JOIN users u2
    ON u2.user_id = 1
   SET    u1.user_permissions = u2.user_permissions
 WHERE  u1.user_id IN (3,4,5);

参见this SQLFiddle