How to swap data in two columns – MySQL

How to swap data in two columns – MySQL

Introduction – Few days back somebody ask me about “How to swap data in two columns?”. Before that I have never thought about such situation…

1] Login to MySQL Server and create Datatabase –

root@IndianGNU:/home/arun.bagul# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 137 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |
+———————————-+
2 rows in set (0.04 sec)

mysql>
mysql> create database arunbagul;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |

| arunbagul |
+———————————+
3 rows in set (0.04 sec)

2 ] Now create Table in ‘arunbagul’ database –

mysql> use arunbagul;
Database changed

mysql> create table student ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY , name varchar(30), surname varchar(30) );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+———————————–+
| Tables_in_arunbagul |
+———————————–+
| student |
+———————————–+
1 row in set (0.00 sec)

mysql>

mysql> desc student;
+———+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| surname | varchar(30) | YES | | NULL | |
+———+————-+——+—–+———+—————-+
3 rows in set (0.01 sec)

mysql>

3 ] Now insert values in ‘student’ table –

mysql> INSERT INTO student VALUES (”,’Arun’ , ‘Bagul’);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT INTO student VALUES (”,’Yogesh’ , ‘Nikam’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Nishit’ , ‘Shah’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Binish’ , ‘Philip’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Deven’ , ‘Jadhav’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from student;
+—-+——–+—————–+
| id | name | surname |
+—-+———-+—————+
| 1 | Arun | Bagul |
| 2 | Yogesh | Nikam |
| 3 | Nishit | Shah |
| 4 | Binish | Philip |
| 5 | Deven | Jadhav |
+—-+——–+———+
5 rows in set (0.00 sec)

mysql>

4 ] Swap data in two cloumns in ‘student’ table –

mysql> UPDATE student as tab1, student as tab2 set tab1.name = tab1.surname, tab1.surname=tab2.name WHERE tab1.id = tab2.id AND tab2.id = tab1.id;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from student;

+—-+———-+—————+
| id | name | surname |
+—-+———-+—————+
| 1 | Bagul | Arun |
| 2 | Nikam | Yogesh |
| 3 | Shah | Nishit |
| 4 | Philip | Binish |
| 5 | Jadhav | Deven |
+—-+———-+————–+
5 rows in set (0.00 sec)

mysql>

Thank you,

Arun Bagul , Prasnna and Devendra

Similar Posts:

Be Sociable, Share!

2 Replies to “How to swap data in two columns – MySQL”

  1. Hi,

    Good idea! using alias names we can do it. I have one more solution to this. Simply swap the column names for two instead of swaping entire data.

    Regards,
    Mohan.

Leave a Reply

Your email address will not be published. Required fields are marked *