December 31, 2010 at 8:43 am
Koen (da-zero) (12/30/2010)
Nice question, but I hope I will never need to swap data between two columns.
You might need to swap if you have a pair of columns such as first_name, last_name and the user enters them in reverse, e.g. "Thomas Mitchell" when it should have been "Mitchell Thomas".
January 2, 2011 at 3:20 pm
Nice question. Though I'm surprised at the high percentage of incorrect answers.
Also, the disclaimer can be removed. This behaviour of the UPDATE statement is how the ANSI standard prescribes it to behave, Any other result, in any version of SQL Server (or even any other relational database) would qualify as a bug. And a severe one.
THe UPDATE should work as if all the results are computed first (based on the old information), and then all rows are updated with the new values instantaneously. That's not actually possible in current technology, but the effect should be the same as if it were possible.
January 5, 2011 at 7:49 pm
Koen (da-zero) (12/30/2010)
jts_2003 (12/30/2010)
...usually when something has gone wrong!;-)That's why I hope I will never need to use it 😉
Although you may never have to simply swap the values of two columns, isn't it valuable to know that the original value of a column remains available within an UPDATE even following code that would appear to modify it?
January 7, 2011 at 6:44 pm
Nice question - although the title does give the answer!
I also wonder about points allocation on these questions - although I don't really care about it - but it seems almost random!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 18, 2011 at 6:07 am
Question title provided answer for the question 😀
January 21, 2011 at 9:41 am
The very reason I got this one wrong was precisely because of posts here that described the "Qurky Update", and thus I was left with the idea that the SET statement's actions operated left to right, in order of appearance in the statement.
I have used that quirky update thing a number of times, and it was so darn handy, that I came to believe something about it that turns out to not be true for a swap field values scenario. Quite interesting, and clearly, a good "teaching moment"...
Many thanks !!!
Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
May 2, 2011 at 11:29 pm
Thanks for the question.
What it does highlight for us 'Newbies' in SQL, is the order of statements being processed, in this case the whole of the SELECT before the FROM is executed as one. 😎
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply