When working with databases, moving and swapping values between columns is a common task.
Frequently, when building and administrating my database, errors in configuration and coding can be fixed by simply swapping values between two different columns.
Let’s start by creating table ’employees’.
if exists ( SELECT name from sys.tables where name ='employees')
drop table employees
GO
CREATE table employees
(
empid int,
fname varchar(20),
lname varchar(20),
deptno int
);
Let’s insert some sample data in the table.
INSERT into employees VALUES (100,'smith','jon',10);
INSERT into employees VALUES (101,'novak','tim',10);
INSERT into employees VALUES (102,'benk','mark',10);
INSERT into employees VALUES (103,'jobs','steve',10);
INSERT into employees VALUES (104,'alex','gravell',20);
Suppose that accidentally we have inserted firstname as lastname and vice versa for department 10. To correct this, we need to swap the firstname and lastname values for that department.
In employees table, firstname =’smith’ and lastname=’jon’, we need to swap these values so the updated table will have firstname=’jon’ and lastname=’smith’ and likewise for all the employees in department 10.
The below query will update the employees table by swapping the values for fname and lname.
SELECT * from employees;
Go
DECLARE @temp as varchar(20)
update employees
set @temp = fname,
fname = lname,
lname = @temp
WHERE deptno = 10;
GO
SELECT * from employees;
The logic for the update query is same as we do in other programming languages. We store the values that are going to be overwritten in a temporary variable and then write back the temporary variable to the correct position.
