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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 8 | next ›
Discussion Overview
groupmysql @
categoriesmysql
postedFeb 5, '08 at 4:26p
activeFeb 7, '08 at 11:06a
posts8
users4
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2021 Grokbase