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.
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
);
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
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:
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.
For More Information https://learn.microsoft.com/en-us/sql/relational-databases/tables/copy-columns-from-one-table-to-another-database-engine
It can be solved by using different attribute.
