SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Swap columns


Swap columns

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3351 Visits: 2149
Comments posted to this topic are about the item Swap columns

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
nicko5
nicko5
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 232
The execution of update statements are not evaluated left to right but as a batch after computations are complete which allows for them to be swapped.
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7638 Visits: 3401
I do not see a value for 2 points. It's only a simple swap. It behaves the same in any sqlserver release.

DECLARE @MyTable TABLE (Col1 INT,
Col2 INT)

-- Col1 Col2
INSERT INTO @MyTable VALUES (1, 2)
INSERT INTO @MyTable VALUES (3, 4)

UPDATE @MyTable SET Col1 = Col2, Col2 = Col1

SELECT Col1, Col2 FROM @MyTable



Try this for fun:
DECLARE @MyTable TABLE (Col1 INT,
Col2 INT
,Col3 INT)
DECLARE @i INT
-- Col1 Col2
INSERT INTO @MyTable(Col1,Col2) VALUES (1, 2)
INSERT INTO @MyTable(Col1,Col2) VALUES (3, 4)
SET @i = 0
UPDATE @MyTable SET
@i = @i + Col1
, Col1 = Col2
, Col2 = Col1
, Col3 = @i

SELECT Col1, Col2, Col3 FROM @MyTable



I run on tuttopodismo
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64762 Visits: 13298
Nice question, but I hope I will never need to swap data between two columns.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
jts2013
jts2013
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 Visits: 5009
Nice Question - it's worth understanding this before you write UPDATE statements that you would expect to work in a procedural language, where the value of col1 would be overwritten by col2, meaning you couldn't then update col2 to the value of what col1 was before.
And yes, you do sometimes need to swap values between columns, or at least recalculate a column based on one or more other columns - usually when something has gone wrong!;-)
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3351 Visits: 2149
jts_2003 (12/30/2010)

...
usually when something has gone wrong!;-)


Exactly - as a matter of fact, that's when this question was thought of.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64762 Visits: 13298
jts_2003 (12/30/2010)
...usually when something has gone wrong!;-)


That's why I hope I will never need to use it ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8236 Visits: 2629
Interesting question. Hopefully I will never need to swap columns. :-)
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 1062
jts_2003 (12/30/2010)
Nice Question - it's worth understanding this before you write UPDATE statements that you would expect to work...


Definitely worthwhile. I did not know an update would work that way. Carlo's example provides further intrigue.
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4115 Visits: 2935
This is how it should happen in any version of SQL server or any SQL database system. This is because SQL is declarative, not procedural. The difference is in the declarative, you're saying "put what's in column 1 into column 2 and what's in column 2 into column 1", whereas in the procedural you'd say "put what's in column 1 into column 2 and then what's in column 2 into column 1". A very tiny difference, but what a difference!

As for Carlos' example with a variable, that's an example of a quirky update, which I first learned of from here: http://www.sqlservercentral.com/articles/T-SQL/68467/ (which references http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/, which appears to be the original source of the term.)

Suffice to say it's a side-effect of the way Microsoft implemented a declarative language (SQL) within a procedural system (x86 software) -- one which works (when programmed meticulously under very specific circumstances), but isn't supported or guaranteed to continue to work in future iterations.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search