Click here to Skip to main content
Click here to Skip to main content
Technical Blog

SQL Server: Simple way to swap column values

, 12 Sep 2012 CPOL
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'),
('B','Y'),
('C','Z')
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily 
ALTER TABLE #ForSwappingTest ADD  Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest
SET COL3 = COL2

UPDATE #ForSwappingTest
SET COL2 = COL1

UPDATE #ForSwappingTest
SET COL1 = COL3
-- Remove additional temporary column
ALTER TABLE #ForSwappingTest DROP COLUMN Col3
--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

License

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

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) 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.
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 4 PinmemberMember 313707823-Sep-12 20:38 
Questiongreat PinmemberAlbarhami13-Sep-12 4:39 
GeneralMy vote of 3 PinmemberAlbarhami13-Sep-12 4:38 
QuestionMy vote of 1 PinmemberJakub Müller12-Sep-12 23:48 
QuestionWhy swap columns in the first place? [modified] PinmvpMika Wendelius12-Sep-12 20:36 
SuggestionAlternate solution (indirect swapping) [modified] PinmemberSandip.Nascar12-Sep-12 8:39 

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

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

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