Copy data from one column to other column (which is in a different table)
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.
In SQL Server 2008 you can use a multi-table update as follows:
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 INTO tblindiantime (CountryName) SELECT BusinessCountry FROM contacts
Here the query:
UPDATE table_name SET column1 = column2
UPDATE table_name1 SET column1 = ( SELECT column2 FROM table_name2 WHERE table_name1.id = table_name2.id );
Read more... Read less...
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 );
- 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)
Hope you have key field is two tables.
UPDATE tblindiantime t SET CountryName = (SELECT c.BusinessCountry FROM contacts c WHERE c.Key = t.Key )
A similar question's answer worked 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.
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):
UPDATE tblindiantime SET CountryName = contacts.BusinessCountry FROM contacts WHERE tblindiantime.contact_id = contacts.id;
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
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.
Now it's more easy with management studio 2016.
Using 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.
2.Click the tab for the table with the columns you want to copy and select those columns.
3.From the Edit menu, click Copy.
4.Open a new Query Editor window.
5.Right-click the Query Editor, and then click Design Query in Editor.
6.In the Add Table dialog box, select the source and destination table, click Add, and then close the Add Table dialog box.
7.Right-click an open area of the the Query Editor, point to Change Type, and then click Insert Results.
8.In the Choose Target Table for Insert Results dialog box, select the destination table.
9.In the upper portion of the Query Designer, click the source column in the source table.
10.The Query Designer has now created an INSERT query. Click OK to place the query into the original Query Editor window.
11.Execute the query to insert the data from the source table to the destination table.