Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
2K+ Views
