- 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
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)
- Related Articles
- Insert data from one table to another in MySQL?
- Simplest way to copy data from one table to another new table in MySQL?
- MySQL statement to copy data from one table and insert into another table
- How do I select data from one table only where column values from that table match the column values of another table in MySQL?
- Move rows from one table to another in MySQL?
- How can we update the values in one MySQL table by using the values of another MySQL table?
- MySQL query to insert data from another table merged with constants?
- Select some data from a database table and insert into another table in the same database with MySQL
- Insert data from one schema to another in MySQL?
- Insert from one table with different structure to another in MySQL?
- How to copy rows from one table to another in MySQL?
- Insert values in a table by MySQL SELECT from another table in MySQL?
- Copy column values from one table into another matching IDs in MySQL
- Insert the data into Table C IF The data is not in Table B while Comparing to Table A in MySQL?
- Find records from one MySQL table which don't exist in another?

Advertisements