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)
Published on 20-Nov-2020 11:31:46
