- Trending Categories
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)
- Related Articles
- How can we create a new MySQL table by selecting specific column/s from another existing table?
- How can we remove a column from MySQL table?
- How can we add a time interval to date stored in a column of MySQL table?
- How to add a row to a table using only strings from another table as reference in MySQL?
- GROUP BY a column in another MySQL table
- How to add a column in a table in MySQL?
- How can we add day/s in the date stored in a column of MySQL table?
- How can we extract a substring from the value of a column in MySQL table?
- Can we use “year” as a column came in a MySQL Table?
- Updating a MySQL table with values from another table?
- How to add a column to a MySQL table in Python?
- Can we remove a primary key from MySQL table?
- Insert values in a table by MySQL SELECT from another table in MySQL?
- How can we create a table from an existing MySQL table in the database?
- How can we put comments in a column of existing MySQL table?
