Can we add a column to a table from another table in MySQL?
Yes, we can add a column to a table from another table. Let us first create two tables. The query to create a table is as follows −
<FirstTable>
mysql> create table FirstTable
-> (
-> UserId int,
-> UserName varchar(20)
-> );
Query OK, 0 rows affected (1.48 sec)
Now create the second table. The query to create the second table is as follows −
<SecondTable>
mysql> create table SecondTable
-> (
-> UserId int,
-> UserAge int
-> );
Query OK, 0 rows affected (1.57 sec)
Now, add column Age to the first table. Firstly, add the Age column, then use UPDATE command to set this Age column to the UserAge column of the SecondTable. The query is as follows −
mysql> ALTER TABLE FirstTable ADD COLUMN Age TINYINT UNSIGNED DEFAULT 0;
Query OK, 0 rows affected (1.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
Now here is the query to update the first table to set the Age column to the UserAge column of the SecondTable. The query is as follows −
mysql> UPDATE FirstTable tbl1
-> INNER JOIN SecondTable tbl2 ON tbl1.UserId = tbl2.UserId
-> SET tbl1.Age = tbl2.UserAge;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
Now check the description of the first table with the help of DESC command. The query is as follows −
mysql> desc FirstTable;
The following is the output displaying that we successfully added a column from the other table −
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| UserId | int(11) | YES | | NULL | |
| UserName | varchar(20) | YES | | NULL | |
| Age | tinyint(3) unsigned | YES | | 0 | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.53 sec)
Published on 19-Mar-2019 12:51:22
