Swap data between two columns in SQL SERVER

Posted on 2006-04-25
Last Modified: 2012-06-22

I have a database table that looks like this:

RecordID     Code          Desc1        Desc2
1                CBY-100     Insert         Mill0.1
2                CBY-101     Insert         Mill1.3
3                CBY-102     Insert         Mill2.4
4                CBY-103     Insert         Mill3.2

I would like to swap out the descriptions so that it looks like this:

RecordID     Code          Desc1        Desc2
1                CBY-100     Mill0.1       Insert
2                CBY-101     Mill1.3       Insert
3                CBY-102     Mill2.4       Insert
4                CBY-103     Mill3.2       Insert

I know how to update one field but i can't seem to do both at the same time. As always any and all help is appreciated.

Question by:Dakid24
    LVL 15

    Assisted Solution

    1. Rename Desc2 to Desc3
    2. Rename Desc1 to Desc2
    3. Rename Desc3 to Desc1
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    Better to rename the column

    exec sp_rename 'TableName.Desc1',Desc3,  'COLUMN'
    exec sp_rename 'TableName.Desc2',Desc1,  'COLUMN'
    exec sp_rename 'TableName.Desc3',Desc2,  'COLUMN'
    LVL 50

    Accepted Solution

    update tablename
      set desc1 = desc2 , desc2 = desc1
     where desc1 <> desc2

    should do it....


    Author Comment

    Thanks for the quick response. All tips worked. I wanted to split up the points three ways by 200 but it wouldn't let me. I took the UPDATE statement as my answer. Thanks for all the help again, you experts rock.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now