Swap columns

Question

Post reply

Swap columns

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

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".

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

john.arnott SSChampion Points: 11882 More actions · Answer 3

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?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

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);

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 5

Question title provided answer for the question 😀

sgmunson SSC Guru Points: 110640 More actions · Answer 6

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

terrykzncs Ten Centuries Points: 1324 More actions · Answer 7

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. 😎