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
