Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Swap column values


Swap column values

Author
Message
Divya Agrawal
Divya Agrawal
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 604
Comments posted to this topic are about the item Swap column values

--Divya
RichB
RichB
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 1023
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?



Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2023 Visits: 797
Saw this last month at another website.

http://www.mssqltips.com/tip.asp?tip=1737
alpeshpradhan
alpeshpradhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 199
why to do unnecessary efforts of using @temp variable?? method is not wrong but its time consuming Smile 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.
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26456 Visits: 38124
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.

Cool
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)
Hari.Sharma
Hari.Sharma
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 455
Its Better to Rename both the columns instead of doing this stuff.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1182 Visits: 5009
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!:-P
alpeshpradhan
alpeshpradhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 199
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]
sknox
sknox
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2136 Visits: 2748
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.
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