Swap value of two columns in a table without using third variable or a table

Question

Swap value of two columns in a table without using third variable or a table

id name lastname
1  ab    cd
2  df    cx
3  sd    gh

I want to swap the two column

id name lastname
1  cd    ab
2  cx    df
3  gh    sd

Answer 1

In almost any database, you can do:

update t
    set name = lastname,
        lastname = name;

It is standard behavior that the first assignment does not affect the second one. The only database where this does not work -- to the best of my knowledge -- is MySQL.

Answer 2

Instead of having to move a lot of data around, it may be easier to create a view with the names you want:

CREATE VIEW myview AS
SELECT lastname AS name, name AS lastname
FROM   mytable

Answer 3

You can try this using inner join update.

Update t1
set t1.name = t2.lastname, t1.lastname = t1.name
from <YourTableName> t1
inner join <YourTableName> t2 on t1.Id = t2.Id

Here is the implementation.

create table #temp (Id int, name varchar(20), lastname varchar(20))
insert into #temp values
('1', 'ab', 'cd'), (2, 'df', 'cx'), (3, 'sd', 'gh')

Update t1
set t1.name = t2.lastname, t1.lastname = t1.name
from #temp t1
inner join #temp t2 on t1.Id = t2.Id

select * from #temp

drop table #temp

The output after update is as below.

Id  name    lastname
--------------------
1   cd     ab
2   cx     df
3   gh     sd

Answer 4

You can simply change column names as per you requirement. To do that perform these following steps-

1. Rename the column 'Name' to a Temporary column name 'Temp'
2. Rename the column name 'LastName' to 'Name'
3. Now rename the column 'Temp' to 'LastName'