- Trending Categories
- 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
Update data in one table from data in another table in MySQL?
For this, you can use UPDATE command along with JOIN.
Let us create the first table −
mysql> create table demo54 −> ( −> firstName varchar(20), −> lastName varchar(20) −> ); Query OK, 0 rows affected (0.57 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo54 values('John','Smith'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo54 values('John','Smith'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo54 values('David','Smith'); Query OK, 1 row affected (0.11 sec)
Display records from the table using select statement −
mysql> select *from demo54;
This will produce the following output −
+-----------+----------+ | firstName | lastName | +-----------+----------+ | John | Smith | | John | Smith | | David | Smith | +-----------+----------+ 3 rows in set (0.00 sec)
Following is the query to create second table −
mysql> create table demo55 −> ( −> firstName varchar(20), −> lastName varchar(20) −> ); Query OK, 0 rows affected (1.93 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo55 (firstName) values('John'); Query OK, 1 row affected (0.10 sec) mysql> insert into demo55 (firstName) values('David'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo55 (firstName) values('Bob'); Query OK, 1 row affected (0.10 sec)
Display records from the table using select statement −
mysql> select *from demo55;
This will produce the following output −
+-----------+----------+ | firstName | lastName | +-----------+----------+ | John | NULL | | David | NULL | | Bob | NULL | +-----------+----------+ 3 rows in set (0.00 sec)
Following is the query to update data in one table from another table.
mysql> UPDATE demo55 tbl1 −> JOIN demo54 tbl2 ON tbl1.firstName = tbl2.firstName −> set tbl1.lastName = tbl2.lastName; Query OK, 2 rows affected (0.10 sec) Rows matched: 2 Changed: 2 Warnings: 0
Display records from the table using select statement −
mysql> select *from demo55;
This will produce the following output −
+-----------+----------+ | firstName | lastName | +-----------+----------+ | John | Smith | | David | Smith | | Bob | NULL | +-----------+----------+ 3 rows in set (0.00 sec)
Advertisements