Hi, On Feb 5, 2008 11:26 AM, Yves Goergen <[EMAIL PROTECTED]> wrote: > Hi, > > I've got an error message from MySQL 5.0 that I don't understand. > > UPDATE "message_revision" SET "HasData" = 1, "Data" = (SELECT "Data" > FROM "message_revision" WHERE "MessageId" = 7 AND "RevisionNumber" = 5) > WHERE "MessageId" = 7 AND "RevisionNumber" = 6 > > SQL error: [SQLSTATE:HY000, 1093] You can't specify target table > 'message_revision' for update in FROM clause > > What went wrong?
You can't select from a table you're updating at the same time. What "at the same time" means is a bit unclear unless you're one of the MySQL developers ;-) However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON .... SET a.col = b.col If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance: UPDATE tbl SET col = ( SELECT ... FROM (SELECT.... FROM) AS x); The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
