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