On Feb 5, 2008 11:26 AM, Yves Goergen wrote:
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
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.