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.