- 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
Add a temporary column in MySQL where the values depend on another column?
You can use CASE statement for this and set conditions to get result in the temporary column.
Let us first create a table −
mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(20), EmployeeSalary int, EmployeeExperience int ); Query OK, 0 rows affected (0.64 sec)
Following is the query to insert some records in the table using insert command −
mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Larry',4500,5); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Mike',130000,8); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Sam',11000,5); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Carol',140000,8) ; Query OK, 1 row affected (0.18 sec)
Following is the query to display records from the table using select command −
mysql> select *from DemoTable;
This will produce the following output −
+------------+--------------+----------------+--------------------+ | EmployeeId | EmployeeName | EmployeeSalary | EmployeeExperience | +------------+--------------+----------------+--------------------+ | 1 | Larry | 4500 | 5 | | 2 | Mike | 130000 | 8 | | 3 | Sam | 11000 | 5 | | 4 | Carol | 140000 | 8 | +------------+--------------+----------------+--------------------+ 4 rows in set (0.00 sec)
Following is the query to add a temporary column in MySQL where the values depend on another column. Here the temporary column is NewSalary −
mysql> select EmployeeId,EmployeeName,EmployeeSalary,EmployeeExperience, case when EmployeeExperience=5 then EmployeeSalary+10000 when EmployeeExperience=8 then EmployeeSalary+20000 else null end as NewSalary from DemoTable;
This will produce the following output −
+------------+--------------+----------------+--------------------+-----------+ | EmployeeId | EmployeeName | EmployeeSalary | EmployeeExperience | NewSalary | +------------+--------------+----------------+--------------------+-----------+ | 1 | Larry | 4500 | 5 | 14500 | | 2 | Mike | 130000 | 8 | 150000 | | 3 | Sam | 11000 | 5 | 21000 | | 4 | Carol | 140000 | 8 | 160000 | +------------+--------------+----------------+--------------------+-----------+ 4 rows in set (0.00 sec)
- Related Articles
- Add a temporary column with a value in MySQL?
- Add records from corresponding duplicate values in another column with MySQL
- Update a column based on another MySQL table’s column
- Is it impossible to add a column in MySQL specifically before another column?
- Find the sum of a column values based on another numerical column in R.
- Concatenate rows on the basis of boolean values in another column with MySQL
- Match column values on the basis of the other two column values in MySQL
- How do I select data from one table only where column values from that table match the column values of another table in MySQL?
- Add a new column and set values in it on the basis of conditions in MySQL?
- Add a character in the end to column values with MySQL SELECT?
- MySQL query to group by column and display the sum of similar values in another column
- MySQL query to select rows where column value is only 0, group by another column?
- Can we add a column to a table from another table in MySQL?
- How to find the column means of a column based on another column values that represent factor in an R data frame?
- How to repeat column values in R matrix by values in another column?

Advertisements