SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Divya Agrawal / Swap column values / Latest Posts

RE: Swap column values

sknox — Wed, 27 May 2009 08:34:17 GMT

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[code]set value1 = value2set value2 = value1[/code]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 [code]set @temp = value1set value1 = value2set value2 = @temp[/code]In T-SQL, however, the code[code]update tableset value1 = value2, value2 = value1[/code]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:[ul][li] 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.[/li][li] 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.)[/li][/ul]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.

RE: Swap column values

alpesh-419376 — Wed, 27 May 2009 08:25:20 GMT

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]

RE: Swap column values

jts_2003 — Wed, 27 May 2009 06:16:25 GMT

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

RE: Swap column values

Hari.Sharma — Wed, 27 May 2009 02:46:07 GMT

Its Better to Rename both the columns instead of doing this stuff.

RE: Swap column values

Lynn Pettis — Tue, 26 May 2009 22:06:23 GMT

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.

RE: Swap column values

alpesh-419376 — Tue, 26 May 2009 06:54:23 GMT

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 thenread from it againjust a simple update query can swap the values provided data types of both columns are same.update SwapDataset value1 = value2 ,value2 = value1 by default sql server keeps old value in memory till transaction is open.

RE: Swap column values

Noel McKinney — Tue, 26 May 2009 06:26:35 GMT

Saw this last month at another website.http://www.mssqltips.com/tip.asp?tip=1737

RE: Swap column values

RichB — Tue, 26 May 2009 04:18:03 GMT

[quote]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=@tempInstead 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 swapdataI 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[/quote]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?

Swap column values

Divya Agrawal — Sun, 24 May 2009 15:23:33 GMT

Comments posted to this topic are about the item [B]Swap column values[/B]