Click here to Skip to main content
13,192,426 members (52,733 online)
Click here to Skip to main content
Add your own
alternative version


1 bookmarked
Posted 12 Sep 2012

SQL Server: Simple way to swap column values

, 12 Sep 2012
Rate this:
Please Sign up or sign in to vote.
A simple way to swap column values.

To resolve a problem, sometimes we start thinking at a high level while simple solutions of said problem are available. This is what happened to me when one of my colleagues (Tehman) asked me how to swap two column values in a table.

My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.

  1. Move Col2 data to Col3
  2. Move Col1 data to Col2
  3. Move Col3 data to Col1
  4. Drop Col3

-- Create Temporary table to hold values
CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))
-- Insert test reocrds
INSERT INTO #ForSwappingTest (Col1,Col2)
VALUES ('A','X'),
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily 
ALTER TABLE #ForSwappingTest ADD  Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest

UPDATE #ForSwappingTest

UPDATE #ForSwappingTest
-- Remove additional temporary column
--Drop temporary table when not required
DROP TABLE #ForSwappingTest

But he came with a very simple solution, by writing the following simple query.

UPDATE #ForSwappingTest
SET Col2 = Col1,
Col1 = Col2


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD ( based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
Member 313707823-Sep-12 19:38
memberMember 313707823-Sep-12 19:38 
Questiongreat Pin
Albarhami13-Sep-12 3:39
memberAlbarhami13-Sep-12 3:39 
GeneralMy vote of 3 Pin
Albarhami13-Sep-12 3:38
memberAlbarhami13-Sep-12 3:38 
QuestionMy vote of 1 Pin
Jakub Müller12-Sep-12 22:48
memberJakub Müller12-Sep-12 22:48 
QuestionWhy swap columns in the first place? Pin
Mika Wendelius12-Sep-12 19:36
mvpMika Wendelius12-Sep-12 19:36 
SuggestionAlternate solution (indirect swapping) Pin
Sandip.Nascar12-Sep-12 7:39
memberSandip.Nascar12-Sep-12 7:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171017.2 | Last Updated 12 Sep 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid