Monday, May 13, 2013

Swap data between columns

Sometime we may mistakenly insert data into a wrong column if we have two identical columns with same data type. To correct this we have to delete the data from the wrong column and insert that in the proper column. Here we had to do lot of work. Swapping can be done :
1.with the help of a temporary variable .

Suppose we have a table named Customer with 3 columns: CustomerID, FName, LName.
              CustomerID: int 
              FName: varchar(35)
              LName: varchar(35)

and we have inserted some rows to the table.

Insert into Customer values(1,'Ladha','Arun')
Insert into Customer values(2,'Daga','Ashit')
Insert into Customer values(3,'Shah','Nishi')
Insert into Customer values(4,'Singh','Ayan')

Here I had inserted FName data in LName and LName data into FName.
Now, I want to swap them.

1. Swap with the help of temporary variable:
DECLARE @temp AS varchar(10)

UPDATE Customer SET @temp=LName, LName=FName, FName=@temp

  The resulting  table can be verified.

UPDATE Customer SET LName = FName, FName = LName

This query can also swap the column data.

No comments:

Post a Comment