On Wed, Aug 02, 2006 at 12:35:30AM +0200, Martin Jespersen wrote:
> I just ran the following sql (on mysql 4.1.20):
>
> update tbl set col1=col2, col2=col1
I went through this recently with the MySQL folks and the long and
short of it is that the above statement is undefined in MySQL. It may
seem to work one way consistently (left-to-right evaulation of
assignments, as you noticed), but they are free to change it whenever
they want.
The case that bit me was when I converted something like the above to
use a multiple-table update, and at that point it evaulated all
right-hand sides in a context where none of the assignments had yet
been done.
The answer was that neither case is guaranteed or defined in MySQL.
The SQL standard seemed to me to define the "all right-hand sides are
evaulated in a context where none of the assignments have been done"
behavior. Perhaps someday MySQL will work that way, but until they
say it does you can't count on any specific behavior.
I suppose that means:
begin transaction
update tbl set tmp=col1, col2=col1
update tbl set col1=tmp
commit
--Pete
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
