合并两个mysql表的一些列，其中id = fileid

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

我要将列cat_id subcat_id med_id移动到表a where表a。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:

编辑:结果应该是表a中的模式更改，因此它看起来如下所示，并且表b中的这些列也有数据:

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?

有什么方法可以轻松做到这一点吗?

3 个解决方案

#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

看到这个SQLFiddle

UPDATE:

更新:

You can change the schema of TableA like this:

可以这样改变表a的模式:

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:

从表b中更新表a的新列如下:

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.

更多信息请参见MySQL: ALTER TABLE语法和MySQL: UPDATE语法。

#2

You can use INNER JOIN on this,

你可以使用内连接，

SELECT  a.*,
        b.cat_id,
        b.subcat_id,
        b.med_ID
FROM    TableA a
        INNER JOIN TableB b
            On a.filedata_id = b.filedataid

SQLFiddle Demo

seeing in your comment, you need to alter the table and add columns on it, to do that you need to execute this DDL statement,

从您的注释中可以看出，您需要修改表并在表上添加列，为此需要执行DDL语句，

ALTER TABLE TableA ADD COLUMN cat_id int;
ALTER TABLE TableA ADD COLUMN subcat_id int;
ALTER TABLE TableA ADD COLUMN med_ID int;

then you can now update the rows of your TableA based on the values of TableB

然后，您可以根据表b的值更新表a的行

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

Hope this makes sense.

希望这是有意义的。

#3

In that case you need to look at the UPDATE at the multiple table section.

在这种情况下，您需要查看多表部分的更新。

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.8.2, “JOIN Syntax”. Here is an example: UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

您还可以执行覆盖多个表的更新操作。但是，不能对多表更新使用ORDER BY或LIMIT。table_references子句列出了连接中涉及的表。它的语法在第13.2.8.2节“连接语法”中描述。这里有一个示例:UPDATE items,month SET items.price=month。价格在items.id = month.id;

Something like

类似的

UPDATE  TableA, TableB
SET     TableA.cat_id = TableB.cat_id,
        TableA.subcat_id = TableB.subcat_id,
        TableA.med_ID = TableB.med_ID
WHERe   TableA.filedata_id = TableB.filedataid

For altering a table (Adding columns to TableA), take a look at ALTER TABLE Syntax

要更改表(向表中添加列)，请查看ALTER table语法