Updating MySQL Blob Field

Question

I'm trying to update several BLOB fields from a value stored in another row in the same table:

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

But this is failing with the following error:

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

Is there a way to accomplish this?

Answer 1

You can achieve this by updating with join like this one:

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);

See this SQLFiddle

Answer 2

I think you can achieve this by using an UPDATE query with an INNER JOIN

UPDATE users a
LEFT JOIN users b
ON a.user_id = b.user_id 
SET a.user_permission = b.user_permission
WHERE a.user_id IN (3,4,5)
AND b.user_id = 1