swapping column values in update
I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this behavior in mysql makes perfect sense, so thats not the issue. I could of course add a temporary col3 to use as a kind of buffer field, and do alter table to add col3 update tbl set col3=col1, col1=col2, col2=col3, col3='' alter table to remove col3 That seems like a waste to me tho. There must be a smarter way. So my question is: Does anyone know of a way to force mysql to read all the values first before actually doing the update? Or just has a smarter way of doing this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: swapping column values in update
At 06:35 PM 8/1/2006, Martin Jespersen wrote: I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this behavior in mysql makes perfect sense, so thats not the issue. If this is a one-time operation, it would seem easier to rename the columns. In some cases, even if it's a little more frequent than that. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: swapping column values in update
it's a frequent operation based on a where clause Barry Newton wrote: At 06:35 PM 8/1/2006, Martin Jespersen wrote: I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this behavior in mysql makes perfect sense, so thats not the issue. If this is a one-time operation, it would seem easier to rename the columns. In some cases, even if it's a little more frequent than that. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: swapping column values in update
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]
