Search code examples
mysqlselectsql-updatesubquery

MySQL update with a subquery

I am trying to update a specific row in a table based on another value in that table, but I can't seem to figure out how to do it:

UPDATE users AS a SET a.val = (SELECT value FROM users WHERE userid = 4) WHERE a.userID = 1

but I am getting the error

Lookup Error - MySQL Database Error: You can't specify target table 'a' for update in FROM clause

Any ideas what I am missing here?

Solution

  • Use JOIN syntax and a non-equi join

    UPDATE users a JOIN users b
    ON a.userID = 1
   AND b.userid = 4
   SET a.value = b.value

    Here is SQLFiddle demo