Re: update with select in MYSQL

[Roland Bouman]

Last line from this page on the UPDATE syntax (see: http://dev.mysql.com/doc/refman/5.0/en/update.html) in MySQL:

"
Currently, you cannot update a table and select from the same table in a subquery.
"

(I know of this only because I ran into trouble some time, and it was pointed out to me by Felix Geerinckx. Man, I wish this limitation was lifted)

I assume that in this case, the subquery is scalar. If so, you can do:

select @b_data:=B_DATA
from SYS_CONFIG_DATA
where VID = 430
and SERVER_ID = 42
;

and immediately after that:

Update SYS_CONFIG_DATA
set B_DATA =@b_data
where VID = 430
and SERVER_ID = 924

(you should lock the table for updates and deletes or perform it all inside a single transaction to avoid the data from being changed in between your statements)