Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server

SQL Server: Simple way to swap column values

Rate me:
Please Sign up or sign in to vote.
3.50/5 (2 votes)
12 Sep 2012CPOL 41.1K   1   6
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

SQL
-- 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.

SQL
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)


Written By
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.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Member 313707823-Sep-12 19:38
Member 313707823-Sep-12 19:38 
Questiongreat Pin
Albarhami13-Sep-12 3:39
Albarhami13-Sep-12 3:39 
GeneralMy vote of 3 Pin
Albarhami13-Sep-12 3:38
Albarhami13-Sep-12 3:38 
QuestionMy vote of 1 Pin
Jakub Müller12-Sep-12 22:48
Jakub Müller12-Sep-12 22:48 
Sorry guy, but your solution is absolutely unnecessary and horrible.

You can switch column values without changing the schema (which is blocking other schema changes, costs a lot of resources, damages statistics, etc ...).

This code do the same thing without schema changing.
SQL
DECLARE @MyTable TABLE ( Col1 VARCHAR(50), Col2 VARCHAR(50) )
INSERT INTO @MyTable (Col1, Col2)
	VALUES ('A','X'),('B','Y'),('C','Z')
	
-- preview of data before change
SELECT * FROM @MyTable

UPDATE MyTarget
	SET Col1 = MySource.Col2
		, Col2 = MySource.Col1
FROM @MyTable AS MyTarget
INNER JOIN @MyTable AS MySource
	-- in typical case is used primary key, not values of all columns
	ON MyTarget.Col1 = MySource.Col1
		AND MyTarget.Col2 = MySource.Col2
		
-- preview of data after change
SELECT * FROM @MyTable

QuestionWhy swap columns in the first place? Pin
Wendelius12-Sep-12 19:36
mentorWendelius12-Sep-12 19:36 
SuggestionAlternate solution (indirect swapping) Pin
Sandip.Nascar12-Sep-12 7:39
Sandip.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.