Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Swap columns Expand / Collapse
Author
Message
Posted Wednesday, December 29, 2010 8:09 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 1,437, Visits: 1,847
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
Post #1040684
Posted Wednesday, December 29, 2010 8:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:04 PM
Points: 137, Visits: 165
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.
Post #1040685
Posted Thursday, December 30, 2010 1:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,570, Visits: 2,428
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

Post #1040757
Posted Thursday, December 30, 2010 2:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1040785
Posted Thursday, December 30, 2010 5:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:25 AM
Points: 1,105, Visits: 4,902
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!
Post #1040835
Posted Thursday, December 30, 2010 6:04 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 1,437, Visits: 1,847
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
Post #1040839
Posted Thursday, December 30, 2010 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1040844
Posted Thursday, December 30, 2010 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Interesting question. Hopefully I will never need to swap columns.
Post #1040864
Posted Thursday, December 30, 2010 7:01 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:15 AM
Points: 1,447, Visits: 1,059
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.
Post #1040874
Posted Thursday, December 30, 2010 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 1,347, Visits: 1,717
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.


Post #1040934
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse