Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
How can we update the values in one MySQL table by using the values of another MySQL table?
For updating the values in one MySQL table by using the values from another MySQL table, we need to use sub-query as an expression in the SET clause of the UPDATE statement.
Example
In this example, we have two table ‘student’ and ‘info’. We will update the value of column ‘grade’ of table ‘student’ by using the values from column ‘remarks’ of ‘info’ table.
mysql> select * from student; +----+---------+-------+ | Id | Name | grade | +----+---------+-------+ | 1 | Rahul | NULL | | 2 | Gaurav | NULL | | 3 | Raman | NULL | | 4 | Harshit | NULL | | 5 | Aarav | NULL | +----+---------+-------+ 5 rows in set (0.01 sec) mysql> select * from info; +------+-----------+ | id | remarks | +------+-----------+ | 1 | Good | | 2 | Good | | 3 | Excellent | | 4 | Average | | 5 | Best | +------+-----------+ 5 rows in set (0.00 sec) mysql> UPDATE STUDENT SET grade = (SELECT remarks from info WHERE info.id = student.id) WHERE id > 0; Query OK, 5 rows affected (0.08 sec) Rows matched: 5 Changed: 5 Warnings: 0
The query above, with the help of sub-query, updates the values in grade column of ‘student’ table. It can be observed from the result set returned by following MySQL query.
mysql> Select * from student; +----+---------+-----------+ | Id | Name | grade | +----+---------+-----------+ | 1 | Rahul | Good | | 2 | Gaurav | Good | | 3 | Raman | Excellent | | 4 | Harshit | Average | | 5 | Aarav | Best | +----+---------+-----------+ 5 rows in set (0.00 sec)
Updated on: 20-Jun-2020
340 Views
Advertisements