Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Swap column values

By Divya Agrawal,

This article is for newbie's who have just started their career in SQL development. Everyone might have faced problems while importing or inserting data into tables. One of the problems can be one column data goes into another column accidentally.

I have faced such problem while importing data from one table into another table which has different field names and accidently I inserted some other columns data into a different column. There was just a minor difference in the column names and all of a sudden many things went wrong.

Whenever such a problem arises, we often become tense and the first solution that comes into our mind is to make another temporary column and using the same old logic for swapping two values: set c = a, a = b, b= c. Then drop the new column, This article would help you learn to interchange the column values without declaring another temporary column.

Say, we have a table named "SwapData"

CREATE TABLE SwapData(id int,value1 varchar(50),value2 varchar(50))
go

Let us insert some values in the table above:

INSERT INTO Swapdata
SELECT 1,10,100
UNION ALL
SELECT 2,20,200
UNION ALL
SELECT 3,30,300
UNION ALL
SELECT 4,40,400
UNION ALL
SELECT 5,50,500
UNION ALL
SELECT 6,60,600
UNION ALL
SELECT 7,70,700
UNION ALL
SELECT 8,80,800
UNION ALL
SELECT 9,90,900
UNION ALL
SELECT 10,11,110

This will insert ten rows having different values in "value1" and "value2".

Now the problem is that the values "value1" and "value2" are in the wrong columns. We must interchange these with each other, putting the data in "value1" in the "value2" column and vice versa.

DECLARE @temp AS varchar(50)
UPDATE swapdata SET @temp=value2,value2=value1,value1=@temp

Instead of declaring a temporary column, we can declare a temporary variable, and it is possible to swap the data of two columns. This statement will first take the value of column "value2" in @temp variable and then take the data of " value1" column in "value2" and finally take the value of @temp in "value1". It's so simple and a much less time consuming way of swapping the data values. Now try selecting the rows of the table.

SELECT * FROM swapdata

I found another solution as well, without the use of any temporary variable. Just execute the following T-SQL and it will swap the column's data.

UPDATE swapdata SET value2=value1,value1=value2

I have tried the same solution for three columns. Let's add a third column "value3" in the "swapdata" table.

ALTER TABLE swapdata ADD value3 varchar(50)
go
UPDATE  swapdata SET value3=value2+'0'
SELECT * FROM swapdata

Let's interchange the values of column as:

"value1" to "value2"

"value2" to "value3"

"value3" to "value1"

We will follow the second solution for attaining the result above.

UPDATE swapdata SET value1=value3,value2=value1,value3=value2

So, next time you face the problem of swap column's data don't get tensed and make the problem worse. This even can be used for swapping data of more number of columns.

Total article views: 1014 | Views in the last 30 days: 4
 
Related Articles
FORUM

SQLSERVER 2000 SQL UNION

SQL UNION

FORUM

Union All

sum..Union all

FORUM

Error/problems with using UNION in combo with CURSOR

invalid column name error using UNION

FORUM

working with union all or union

union all vs union

BLOG

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they......

Tags
swap data    
t-sql    
update    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones