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

Swap column values Expand / Collapse
Author
Message
Posted Sunday, May 24, 2009 3:23 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 9:42 AM
Points: 141, Visits: 527
Comments posted to this topic are about the item Swap column values

--Divya
Post #722782
Posted Tuesday, May 26, 2009 4:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:22 AM
Points: 1,052, Visits: 862
Now the problem is that the values "value1" and "value2" are in the wrong columns. We must interchange these with each other, putting the data in "value1" in the "value2" column and vice versa.

DECLARE @temp AS varchar(50)
UPDATE swapdata SET @temp=value2,value2=value1,value1=@temp
Instead of declaring a temporary column, we can declare a temporary variable, and it is possible to swap the data of two columns. This statement will first take the value of column "value2" in @temp variable and then take the data of " value1" column in "value2" and finally take the value of @temp in "value1". It's so simple and a much less time consuming way of swapping the data values. Now try selecting the rows of the table.

SELECT * FROM swapdata
I found another solution as well, without the use of any temporary variable. Just execute the following T-SQL and it will swap the column's data.

UPDATE swapdata SET value2=value1,value1=value2



So an entire article based around you finding an obscurely complicated way of doing something simple, then as an afterthought putting in the simple, more obvious solution?

Or have I missed something?



Post #723149
Posted Tuesday, May 26, 2009 6:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
Saw this last month at another website.

http://www.mssqltips.com/tip.asp?tip=1737
Post #723201
Posted Tuesday, May 26, 2009 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 6:11 AM
Points: 14, Visits: 196
why to do unnecessary efforts of using @temp variable?? method is not wrong but its time consuming :) as its will firstly write value in variable and then
read from it again

just a simple update query can swap the values provided data types of both columns are same.

update SwapData
set value1 = value2
,value2 = value1

by default sql server keeps old value in memory till transaction is open.


Post #723224
Posted Tuesday, May 26, 2009 10:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:25 PM
Points: 22,525, Visits: 30,288
I find the critism of a relative newbie who has taken the time to write an article showing people something they have learned a little much. It may have better to simply acknowledge that the method discussed was viable and that they also discovered a slightly better alternative while working on the article.

Why must everyone have to be so critical? We as professionals really need to be more supportive.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #723753
Posted Wednesday, May 27, 2009 2:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Its Better to Rename both the columns instead of doing this stuff.

Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #723874
Posted Wednesday, May 27, 2009 6:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 1,082, Visits: 4,888
I just wanted to say I was glad this was posted, even if not the perfect solution, as the debate has flagged up something I wasn't sure of: namely "by default sql server keeps old value in memory till transaction is open" - so, even if no-one else did, I learnt something!
Post #724000
Posted Wednesday, May 27, 2009 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 6:11 AM
Points: 14, Visits: 196
Lynn...no hard feelings about post on forum but i felt its important to raise a point about using that @temp variable.
here i am not criticizing anyone...but just informing [Smile] and I think purpose of forum is that only!
lets say we have 5-10gb of table in which we want to swap columns, in this case using @temp variable will be too expensive.

guys...I really appreciate the efforts and time you have taken to post here. keep posting... [Smile]

Post #724157
Posted Wednesday, May 27, 2009 8:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 1,251, Visits: 1,596
I think this was a very useful article, because it highlighted a difference we sometimes forget between T-SQL and procedural code. Specifically, procedural code like

set value1 = value2
set value2 = value1

is executed as a set of statements which must be completed separately and in order. In a procedural language the above code would set the two variables equal to the same value, so a procedural programmer would naturally rewrite the code as

set @temp = value1
set value1 = value2
set value2 = @temp

In T-SQL, however, the code

update table
set value1 = value2,
value2 = value1

is a single statement, which means it happens all at once. So value1 will not be updated to =value2 until after the statement is complete (i.e, after value2 is =value1). Or, to simplify, in T-SQL, any references after the = in a set refer to the values before the statement executes.

As far as just renaming the columns, that works, but only if:

  • All of the values need to be swapped. If, for example, you only need to swap some values because one data entry person got their fields mixed up, renaming columns will just make things worse.

  • All code that accesses this table uses column names and not ordinal positions. I have seen code written by others which used the ordinal positions instead of the column names, and I myself have run into one situation (using VBScript and ADO) where for some reason it would not work using column names. (No, I didn't leave that using ordinal position -- I changed it to use ordinal position to get it working immediately, and then rewrote the routine when the system wasn't in use.)



So there are definitely reasons to know how to swap values, and it's good to be reminded how T-SQL allows for simpler coding of set-based operations than most procedural languages.
Post #724170
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse