How to add a column to a table from another table in Mysql?

mysql sql select sql-update alter
Codesl picture Codesl · Dec 9, 2014 · Viewed 68.5k times · Source

I have two tables

  1. table1
  2. table2

Tabel1 contains 2 columns

  1. id
  2. Name

Tabel2 contains 2 columns

  1. id
  2. Age

A want to add age column from table2 to table1 (WHERE table1.id = table2.id)

Then table1 should contains 3 columns

  1. id
  2. Name
  3. Age

Answer

fancyPants picture fancyPants · Dec 9, 2014

First add the column with the appropriate datatype.

ALTER TABLE table1 ADD COLUMN Age TINYINT UNSIGNED NOT NULL DEFAULT 0;

Then update the table, so that the values are "transmitted".

UPDATE table1 t1
INNER JOIN tabel2 t2 ON t1.id = t2.id 
SET t1.Age = t2.Age

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More