Merging some columns of two mysql tables where id = fileid

Question

There are two tables

TableA
filedata_id | user_id | filename
1           | 1       | file.txt
2           | 1       | file2.txt


TableB   
a_id        | date    | filedataid | counter | state | cat_id | subcat_id | med_id
99          | 1242144 | 1          | 2       | v     | 55     |  56       | 90
100         | 1231232 | 2          | 3       | i     | 44     |  55       | 110

I want to move columns cat_id, subcat_id, med_id to TableA where tableA.filedata_id = TableB.filedataid

edit: The result should be a schema change in tableA so it looks like the following and also have the data from those columns in tableB:

TableA
filedata_id | user_id | filename  | cat_id | subcat_id | med_id
1           | 1       | file.txt  | 55     | 56        | 90
2           | 1       | file2.txt | 44     | 55        | 110

and so on.

Is there a way to do this easily?

Answer 1

You can use INNER JOIN for that:

SELECT t1.filedata_id, t1.user_id, t1.filename
      ,t2.cat_id, t2.subcat_id, t2.med_id
FROM TableA t1 
INNER JOIN TableB t2 
ON t1.filedata_id = t2.filedataid

See this SQLFiddle

UPDATE:

You can change the schema of TableA like this:

ALTER TABLE TableA
Add column cat_id int, 
Add column  subcat_id int, 
Add column  med_id int;

And update new columns of TableA from TableB like this:

UPDATE tableA t1 
       JOIN tableB t2 
       ON t1.filedata_id = t2.filedataid
SET t1.cat_id = t2.cat_id,
    t1.subcat_id = t2.subcat_id,
    t1.med_ID = t2.med_ID;

See this SQLFiddle

For more see MySQL: ALTER TABLE Syntax and MySQL: UPDATE Syntax.