MYSQL，将选定的字段从一个表复制到另一个

MYSQL, Copy selected fields from one table to another

提问by ix3

In MySQL, How do I copy a FIELD with all RECORDS from TABLE1to TABLE2which corresponds to a primary key ie: EMPLOYEE no.?

在 MySQL 中，如何将包含所有记录的 FIELD 复制TABLE1到TABLE2对应于主键的字段，即：EMPLOYEE no.？

回答by Andriy M

If you mean you want to updateone table's column using another table's column, then here are some options:

如果您的意思是要使用另一个表的列更新一个表的列，那么这里有一些选项：

1. A join:

   UPDATE table1 AS t1
     INNER JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   Alternatively it could be a left join:

   UPDATE table1 AS t1
     LEFT JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   which would essentially empty (set to NULL) the values where no match occurred.

2. A subquery:

   UPDATE table1
   SET SomeColumn = (
     SELECT SomeColumn
     FROM table2
     WHERE EmployeeNo = table1.EmployeeNo
   )
   

   This is equivalent to the left join solution in #1.

1. 加入：

   UPDATE table1 AS t1
     INNER JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   或者，它可以是左连接：

   UPDATE table1 AS t1
     LEFT JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   这将基本上清空（设置为 NULL）未发生匹配的值。

2. 一个子查询：

   UPDATE table1
   SET SomeColumn = (
     SELECT SomeColumn
     FROM table2
     WHERE EmployeeNo = table1.EmployeeNo
   )
   

   这等效于 #1 中的左连接解决方​​案。

Note that in all cases it is assumed that a row in table1can match no more than one row in table2.

请注意，在所有情况下，都假定 in 中的一行table1只能匹配 中的一行table2。

回答by Anand

Try this

尝试这个

INSERT INTO `table2` (`field_name2`) SELECT `field_name` FROM `table1`

回答by php

The query for copy data from one table to another is:

将数据从一个表复制到另一个表的查询是：

Insert into table2 (field1, field2)  select field1, field2 from table1

If you want to copy only selected values, then use where clause in query

如果只想复制选定的值，请在查询中使用 where 子句

Insert into table2 (field1, field2)  select field1, field2 from table1 where field1=condition

回答by brigitte18

update
  table1 t1
  join table2 t2 on t2.field = t1.field
set
  t1.field1 = t2.matchingfield
where
  t1.whatever = t2.whatever

回答by Ntaganira

You can use this to copy all the records from table1into table2with a condition.

您可以使用它来将所有记录复制table1到table2一个条件中。

Insert into table2  select  * from table1 where field1=condition

回答by Gaurav Gupta

Suppose if the table structure is as follows.

假设表结构如下。

TableA - Col1, Col2 ,Col3 TableB - Col1, Col2 ,Col3

表A - Col1, Col2 ,Col3 表B - Col1, Col2 ,Col3

There is no need to select all column of the table to transfer data from 1 table to another table in same databse. You can copy (insert) the rows from TableA to TableB.

不需要选择表的所有列来将数据从一个表传输到同一数据库中的另一个表。您可以将行从 TableA 复制（插入）到 TableB。

Code as follows -

代码如下——

Insert into TableB (Col1, Col2 ,Col3)
Select Col1, Col2 ,Col3 from TableA

You can also do this -

你也可以这样做——

Insert into TableB (Col1, Col2, Col3) 
Select * from TableA

Both codes work , you need to see your requirement.

两个代码都可以工作，您需要查看您的要求。

Generic code -

通用代码——

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

You can add 'Where' condition if you need.

如果需要，您可以添加“Where”条件。

Thank you!!!

谢谢！！！

回答by K. Salabir

INSERT INTO table_1(column-1, column-2) SELECT column-1, column-2 FROM table_2;

INSERT INTO table_1( column-1, column-2) SELECT column-1, column-2 FROM table_2;

回答by Shyam Kumar

Insert into Delivery (DeliveredDate, appid, DownloadSize, UploadSize) select Delivered, Appid, DownloadSize,UploadSize from Delivery_Summary;

Insert into Delivery (DeliveredDate, appid, DownloadSize, UploadSize) select Delivered, Appid, DownloadSize,UploadSize from Delivery_Summary;