Swap SQL Server column data due to inserting data into wrong columns


By:   |   Updated: 2009-04-24   |   Comments (4)   |   Related: More > T-SQL


Problem

This article is for newbies who have just started their career in SQL development.  Everyone might have faced this problem when importing or inserting data into tables when they accidentally put the data in the wrong columns. The first thought would be to delete the data and start over, but in this tip we look at a simple solution to swap the data. 

Solution

To illustrate this problem I am going to create a simple table with three columns that have the same data type as shown below.

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

Now that the table has been created, let's insert some values in the table. Here I am using a UNION ALL statement so I can do one INSERT, but this could also be done by issuing several insert statements.

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" as shown in the image below.

sql result set 1

Now that are data is inserted we can see that the larger values should have been in "value1".  So now we need to swap the values of "value1" and "value2".

 

There are various solutions to this problem. As discussed in the problem statement you could delete the data and start over again or you could create a new column and move the data, but there are easier options.

One of these easier options is shown below where a temporary variable is created which then allows you to update the data and swap the column values.

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

Instead of creating a temporary column in the table. this can be done by declaring a temporary variable to swap the data in the two columns.  The code above is explained below.

  • First create a temporary variable
  • Then put the data of column "value2" in the @temp variable
  • Then put the data of column "value1" in the "value2" column
  • Finally take the value of @temp and put it in "value1" column

Now, try selecting the rows of the table.

SELECT * FROM swapdata

And we can see that our table has been updated correctly.

sql result set 2

But the solution which I found the best is without the use of any temporary variable.

Just execute the following T-SQL and it will swap the columns data. 

UPDATE swapdata SET value2=value1,value1=value2

This moves the data from value1 to value 2 and the data from value2 to value1 at the same time.

Now, try selecting the rows of the table and we can see the data has been swapped back to the original.

SELECT * FROM swapdata

sql result set 3

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

sql result set 4

Let's interchange the values of the columns as:

  • "value1" gets "value3" data
  • "value2" gets "value1" data
  • "value3" gets "value2" data

We will follow the second solution for attaining the result.

UPDATE swapdata SET value1=value3,value2=value1,value3=value2
  
SELECT * FROM swapdata

sql result set 5

Note that the series of swapping columns can be given in any order. Just make sure the value is passed as per the requested output. I think this would be very helpful to everyone, just interchanging column values without having the headache of temporary columns and variables.

So next time you face the problem of having to swap data among columns in a table just remember this simple solution.

Next Steps
  • This can be used for more columns as well and also by joining multiple tables.
  • It can even be used by adding WHERE conditions in the update statement, so that whenever the data for a particular range has to be swapped it is possible


Last Updated: 2009-04-24


get scripts

next tip button



About the author
MSSQLTips author Divya Agrawal Divya Agrawal's bio is coming soon...

View all my tips



Comments For This Article




Tuesday, August 20, 2013 - 12:56:29 PM - Oracle8202 Back To Top (26393)

Does anybody know how to get this to work with multiple tables without a variable?  Something like

 

UPDATE tb1

SET tb1.colA = tb2.colB, tb2.colB = tb1.colA

FROM tb1 INNER JOIN tb2 ON tb1.id = tb2.id

 

I see the author states "This can be used for ... joining multiple tables." but I don't see any examples.


Sunday, August 12, 2012 - 5:56:52 AM - Anoop Kumar Shrivastava Back To Top (19004)

This article is very impressive as well as useful for fastest working with SQL and/or databases

Thanks


Friday, December 17, 2010 - 3:03:15 AM - DJ Back To Top (10462)

Impressive article :)


Friday, May 15, 2009 - 10:55:30 PM - RM Joseph Back To Top (3378)

The swap solution is simplified and easy to understand.  This tips will be useful at time of emergency while working in DB

R. M. Joseph



download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

SQL Server DROP TABLE IF EXISTS Examples














get free sql tips
agree to terms