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's first create two tables. The query to create the table is as follows -

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 -

mysql> create table SecondTable
   -> (
   -> UserId int,
   -> UserAge int
   -> );
Query OK, 0 rows affected (1.57 sec)

Now, add the age column to the first table. First, add an Age column, and then use the UPDATE command to set this Age column to the SecondTable's UserAge column. 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, this is the query that updates 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 showing that we have successfully added a column from another 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)

The above is the detailed content of Can we add a column to a table from another table in MySQL?. For more information, please follow other related articles on the PHP Chinese website!