

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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 Questions & Answers
- 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
- Move rows from one table to another 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?
- Insert data from one schema to another in MySQL?
- MySQL query to insert data from another table merged with constants?
- How can we update the values in one MySQL table by using the values of another MySQL table?
- Insert from one table with different structure to another in MySQL?
- How to copy rows from one table to another in MySQL?
- Select some data from a database table and insert into another table in the same database with 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
- Updating a MySQL table with values from another table?
- How to copy a table from one MySQL database to another?
Advertisements