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

Published on 20-Nov-2020 07:01:46

Previous Page Print Page