SQL Server: How to Swap Values between Two Columns

Posted October 7, 2012 by Vishwanath Dalvi in Database, SQL Server

When working with databases, moving and swapping values between columns is a common task.

Frequently, when building and administrating my database, errors in configuration and coding can be fixed by simply swapping values between two different columns.

Let’s start by creating table ’employees’.

if exists ( SELECT name from sys.tables where name ='employees')
drop table employees
CREATE table employees
empid int,
fname varchar(20),
lname varchar(20),
deptno int

Let’s insert some sample data in the table.

INSERT into employees VALUES (100,'smith','jon',10);
INSERT into employees VALUES (101,'novak','tim',10);
INSERT into employees VALUES (102,'benk','mark',10);
INSERT into employees VALUES (103,'jobs','steve',10);
INSERT into employees VALUES (104,'alex','gravell',20);

Suppose that accidentally we have inserted firstname as lastname and vice versa for department 10. To correct this, we need to swap the firstname and lastname values for that department.

In employees table, firstname =’smith’ and lastname=’jon’, we need to swap these values so the updated table will have firstname=’jon’ and lastname=’smith’ and likewise for all the employees in department 10.

The below query will update the employees table by swapping the values for fname and lname.

SELECT * from employees;

DECLARE @temp as varchar(20)
update employees
set    @temp = fname,
       fname = lname,
       lname = @temp
WHERE  deptno = 10;

SELECT * from employees;


Swap values between two columns


The logic for the update query is same as we do in other programming languages. We store the values that are going to be overwritten in a temporary variable and then write back the temporary variable to the correct position.


About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Chris key

    You realise you aren’t even using the temp variable… Swapping columns is easier in sql compared to other languages as the updates all happen in parallel, you don’t have to worry about chain updates in the same row.

    • Vishwanath Dalvi

      @Chris Thanks for valuable input, I’ve included the temp variable and yes the updates are happens in parallel.

  • Adrian

    You do realise that in Sql 2008 at least you can just do the following
    Update Table Set Field1=Field2, Field2=Field1

    and it just works.


    • Vishwanath Dalvi

      Yes, RDBMS doesn’t follow the conventional approach as other programming languages, Thanks you for letting us know.