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:

• Indexes in MySQL
• built-in SQL functions
• 을 구성하는 방법 MySQL은 다중 마스터 복제
• 如何配置MySQL的多主复制
• を構成する方法マルチマスターのMySQLレプリケーション
• Как настроить несколько мастер репликации MySQL
• วิธี การ กำหนด ค่า หลาย master ซ้ำ MySQL