[MySQL] Error: You can't specify target table '...' for update in FROM clause

[MySQL] Error: You can't specify target table '...' for update in FROM clause

	Yves Goergen
	Feb 5, 2008 at 4:26 pm

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?

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de

Search Discussions

Discussion Posts

Follow ups

Baron Schwartz: Hi, 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
Yves Goergen: Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. That sounds interesting, however, I
Chris: Update table set blob2_field=blob1_field;

Related Discussions

How to use "use" for a specific host
can't drop innodb table
' . . ' can't insert into a date field,but update can
Problem with UPDATE syntax when updating multiple tables
Save HTML code
updating table based on other table values
If Exists update else insert
replication, multi-table update work around
How to create temporary table in 4.0?
update query confusion

Discussion Navigation

view	thread \| post
posts	‹ prev \| 1 of 8 \| next ›

Discussion Overview

group	mysql @
categories	mysql
posted	Feb 5, '08 at 4:26p
active	Feb 7, '08 at 11:06a
posts	8
users	4
website	mysql.com
irc	#mysql

font	variable	Hotkey: f
user style	avatars	Hotkey: a

[MySQL] Error: You can't specify target table '...' for update in FROM clause

Search Discussions

Discussion Posts

Related Discussions

4 users in discussion