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)

AmitDiwan

Updated on 20-Nov-2020 07:03:10

• 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?