SQL Server: How to Swap Values between Two Columns

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
GO
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;
Go

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

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.

4 Responses to “SQL Server: How to Swap Values between Two Columns”

  1. October 08, 2012 at 11:18 am, Chris key said:

    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.

    Reply

    • October 08, 2012 at 11:31 am, Vishwanath Dalvi said:

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

      Reply

  2. November 22, 2012 at 3:17 am, Adrian said:

    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.

    http://www.dbforums.com/microsoft-sql-server/996161-challenge-swap-two-columns.html

    Reply

    • November 22, 2012 at 7:23 am, Vishwanath Dalvi said:

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

      Reply

Leave a Reply