SQL 将数据从一列复制到另一列（位于不同的表中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3361768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy data from one column to other column (which is in a different table)
提问by Amit Patil
I want to copy data from one column to another column of other table. How can I do that?
我想将数据从一列复制到其他表的另一列。我怎样才能做到这一点？
I tried the following:
我尝试了以下方法：
Update tblindiantime Set CountryName =(Select contacts.BusinessCountry From contacts)
but it did not work.
但它没有用。
I want to copy "BusinessCountry" column of contact table to "CountryName" column of tblindiantime table.
我想将联系人表的“BusinessCountry”列复制到 tblindiantime 表的“CountryName”列。
采纳答案by Mark Byers
In SQL Server 2008 you can use a multi-table update as follows:
在 SQL Server 2008 中，您可以使用多表更新，如下所示：
UPDATE tblindiantime
SET tblindiantime.CountryName = contacts.BusinessCountry
FROM tblindiantime
JOIN contacts
ON -- join condition here
You need a join condition to specify which row should be updated.
您需要一个连接条件来指定应该更新哪一行。
If the target table is currently empty then you should use an INSERT instead:
如果目标表当前为空，则应改用 INSERT：
INSERT INTO tblindiantime (CountryName)
SELECT BusinessCountry FROM contacts
回答by Nalan Madheswaran
Here the query:
这里查询：
Same Table:
同表：
UPDATE table_name
SET column1 = column2
Different Table:
不同的表：
UPDATE table_name1
SET column1 = (
SELECT column2
FROM table_name2
WHERE table_name1.id = table_name2.id
);
回答by Kenny Grage
Table2.Column2 => Table1.Column1
Table2.Column2 => Table1.Column1
I realize this question is old but the accepted answer did not work for me. For future googlers, this is what worked for me:
我意识到这个问题很老，但接受的答案对我不起作用。对于未来的谷歌员工，这对我有用：
UPDATE table1
SET column1 = (
SELECT column2
FROM table2
WHERE table2.id = table1.id
);
Whereby:
据此：
- table1 = table that has the column that needs to be updated
- table2 = table that has the column with the data
- column1 = blank column that needs the data from column2 (this is in table1)
- column2 = column that has the data (that is in table2)
- table1 = 包含需要更新的列的表
- table2 = 包含数据列的表
- column1 = 需要 column2 中数据的空白列（这是在 table1 中）
- column2 = 包含数据的列（在 table2 中）
回答by Michael Pakhantsov
Hope you have key field is two tables.
希望您的关键字段是两个表。
UPDATE tblindiantime t
SET CountryName = (SELECT c.BusinessCountry
FROM contacts c WHERE c.Key = t.Key
)
回答by cedricdlb
A similar question's answerworked more correctly for me than this question's selected answer(by Mark Byers). Using Mark's answer, my updated column got the same value in all the rows (perhaps the value from the first row that matched the join). Using ParveenaArora's answer from the other thread updated the column with the correct values.
一个类似问题的答案对我来说比这个问题的选择答案（马克拜尔斯）更正确。使用 Mark 的回答，我更新的列在所有行中都获得了相同的值（可能是与连接匹配的第一行的值）。使用来自另一个线程的 ParveenaArora 的回答，用正确的值更新了该列。
Transforming Parveena's solution to use this question' table and column names, the query would be as follows (where I assume the tables are related through tblindiantime.contact_id):
将 Parveena 的解决方案转换为使用此问题的表和列名称，查询如下（我假设这些表通过 tblindiantime.contact_id 相关）：
UPDATE tblindiantime
SET CountryName = contacts.BusinessCountry
FROM contacts
WHERE tblindiantime.contact_id = contacts.id;
回答by Muhammad Ramahy
I think that all previous answers are correct, this below code is very valid specially if you have to update multiple rows at once, note: it's PL/SQL
我认为以前的所有答案都是正确的，下面的代码非常有效，特别是如果您必须一次更新多行，请注意：它是 PL/SQL
DECLARE
CURSOR myCursor IS
Select contacts.BusinessCountry
From contacts c WHERE c.Key = t.Key;
---------------------------------------------------------------------
BEGIN
FOR resultValue IN myCursor LOOP
Update tblindiantime t
Set CountryName=resultValue.BusinessCountry
where t.key=resultValue.key;
END LOOP;
END;
I wish this could help.
我希望这会有所帮助。
回答by Coder
Now it's more easy with management studio 2016.
现在使用 management studio 2016 更容易。
Using SQL Server Management Studio
使用 SQL Server Management Studio
To copy data from one table to another
将数据从一张表复制到另一张表
1.Open the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design.
1.通过右键单击表格，然后单击设计，打开包含要复制的列和要复制到的列的表格。
2.Click the tab for the table with the columns you want to copy and select those columns.
2. 单击包含要复制的列的表的选项卡并选择这些列。
3.From the Editmenu, click Copy.
3.从编辑菜单中，单击复制。
4.Open a new Query Editor window.
4.打开一个新的查询编辑器窗口。
5.Right-click the Query Editor, and then click Design Query in Editor.
5.右键单击查询编辑器，然后单击在编辑器中设计查询。
6.In the Add Tabledialog box, select the source and destination table, click Add, and then close the Add Tabledialog box.
6.在“添加表”对话框中，选择源表和目标表，单击“添加”，然后关闭“添加表”对话框。
7.Right-click an open area of the the Query Editor, point to Change Type, and then click Insert Results.
7. 右键单击查询编辑器的打开区域，指向更改类型，然后单击插入结果。
8.In the Choose Target Table for Insert Resultsdialog box, select the destination table.
8.在“为插入结果选择目标表”对话框中，选择目标表。
9.In the upper portion of the Query Designer, click the source column in the source table.
9. 在查询设计器的上部，单击源表中的源列。
10.The Query Designer has now created an INSERT query. Click OK to place the query into the original Query Editor window.
10.查询设计器现在已经创建了一个 INSERT 查询。单击确定将查询放入原始查询编辑器窗口。
11.Execute the query to insert the data from the source table to the destination table.
11.执行查询将数据从源表插入到目标表。
For More Information https://docs.microsoft.com/en-us/sql/relational-databases/tables/copy-columns-from-one-table-to-another-database-engine
回答by user5204562
It can be solved by using different attribute.
它可以通过使用不同的属性来解决。
- Use the cell Control click event.
- Select the column value that your transpose to anther column.
- send the selected value to the another text box or level whatever you fill convenient and a complementary button to modify the selected property.
- update the whole stack op the database and make a algorithm with sql query to overcome this one to transpose it into the another column.
- 使用单元格控件单击事件。
- 选择您转置到另一列的列值。
- 将选定的值发送到另一个文本框或级别，无论您填写方便，还是一个补充按钮来修改选定的属性。
- 更新整个堆栈操作数据库，并使用 sql 查询创建算法来克服这个问题，将其转置到另一列中。
