May 24, 2009 at 3:23 pm
Comments posted to this topic are about the item Swap column values
--Divya
May 26, 2009 at 4:18 am
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?
May 26, 2009 at 6:26 am
Saw this last month at another website.
May 26, 2009 at 6:54 am
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.
May 26, 2009 at 10:06 pm
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.
May 27, 2009 at 2:46 am
Its Better to Rename both the columns instead of doing this stuff.
May 27, 2009 at 6:16 am
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
May 27, 2009 at 8:25 am
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]
May 27, 2009 at 8:34 am
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:
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply