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)

Samual Sam

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• How can we remove a column from MySQL table?
• How can we create a new MySQL table by selecting specific column/s from another existing table?
• Updating a MySQL table with values from another table?
• How can we add a time interval to date stored in a column of MySQL table?
• GROUP BY a column in another MySQL table
• How to add a row to a table using only strings from another table as reference in MySQL?
• Can we remove a primary key from MySQL table?
• How to add a column in a table in MySQL?
• How can we extract a substring from the value of a column in MySQL table?
• Insert values in a table by MySQL SELECT from another table in MySQL?
• How to add a column to a MySQL table in Python?
• How can we create a table from an existing MySQL table in the database?
• How can we delete a single row from a MySQL table?
• How can we drop UNIQUE constraint from a MySQL table?
• How can we delete multiple rows from a MySQL table?