Swap column values

  • Comments posted to this topic are about the item Swap column values

    --Divya

  • 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?

  • Saw this last month at another website.

    http://www.mssqltips.com/tip.asp?tip=1737

  • 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.

  • 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.

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

  • 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

  • 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]

  • 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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply