- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Swapping two column values in MySQL?
To swap two columns, we can apply the below swapping logic.
Add both values and store them into the first column
Subtract the first column’s value from the second and store it into the second column.
Subtract the first column’s value from the updated second column and store it into the first.
The above rule structure is as follows. Suppose, the first column is a and the second column is b.
1. a = a+b; 2. b = a-b; 3. a = a-b;
Now we will apply the above rule in order to swap the two column values.
Creating a table.
mysql> create table SwappingTwoColumnsValueDemo -> ( -> FirstColumnValue int, -> SecondColumnValue int -> ); Query OK, 0 rows affected (0.49 sec)
Inserting some records.
mysql> insert into SwappingTwoColumnsValueDemo values(10,20),(30,40),(50,60),(70,80),(90,100); Query OK, 5 rows affected (0.19 sec) Records: 5 Duplicates: 0 Warnings: 0
To check the column values before swapping.
mysql> select *from SwappingTwoColumnsValueDemo;
The following is the output.
+------------------+-------------------+ | FirstColumnValue | SecondColumnValue | +------------------+-------------------+ | 10 | 20 | | 30 | 40 | | 50 | 60 | | 70 | 80 | | 90 | 100 | +------------------+-------------------+ 5 rows in set (0.00 sec)
Syntax to swap column values.
mysql> UPDATE SwappingTwoColumnsValueDemo -> SET FirstColumnValue = FirstColumnValue+SecondColumnValue, -> SecondColumnValue = FirstColumnValue-SecondColumnValue, -> FirstColumnValue = FirstColumnValue-SecondColumnValue; Query OK, 5 rows affected (0.15 sec) Rows matched: 5 Changed: 5 Warnings: 0
To check if the column values have been swapped or not.
mysql> select *from SwappingTwoColumnsValueDemo;
The following is the output.
+------------------+-------------------+ | FirstColumnValue | SecondColumnValue | +------------------+-------------------+ | 20 | 10 | | 40 | 30 | | 60 | 50 | | 80 | 70 | | 100 | 90 | +------------------+-------------------+ 5 rows in set (0.00 sec)
- Related Articles
- How to update a MySQL table by swapping two column values?
- Match column values on the basis of the other two column values in MySQL
- Select a specific value between two column values in MySQL?
- Concatenate two values from the same column with different conditions in MySQL
- How to concatenate two column values into a single column with MySQL. The resultant column values should be separated by hyphen
- Get all the records with two different values in another column with MySQL
- Multiply values of two columns and display it a new column in MySQL?
- Concatenate two columns when one of such column values is null in MySQL
- Shuffling column values with MySQL?
- MySQL query to match any of the two strings from column values
- MySQL query to get first two highest column values from a table?
- Set ENUM in MySQL for column values
- MySQL randomly select 2 values from column values?
- Sum the values in a column in MySQL?
- Setting column values as column names in the MySQL query result?

Advertisements